Move SQL Database from one drive to another

This is a common task most of DBA’s perform regularly. You face this situation when your data file drive is full and windows team cannot add disk space or you cannot clear any space in drive.

Task:

We have data file located in C:\SQL_2K14\M\MSSQL_DATA\Audit.mdf

Above data file should be moved to G:\MSSQL_DATA

This task takes database offline; make sure you have all the appropriate approvals needed for this Change. (This task needs change ticket)

Step 1: Check the current location of database files.

USE master

GO

SELECT name AS LogicalFileName, physical_name AS FileLocation

, state_desc AS Status

FROM sys.master_files

WHERE database_id = DB_ID('Audit');

Step 2: Make database offline.

USE master

GO

ALTER DATABASE Audit SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

*** All incomplete transactions will be rolled back and any other connections to the Audit database will be immediately disconnected. ***

Step 3: Move the database file from C drive to G drive physically.

Step 4: Modify the logical name and path of data file (new path).

USE master

GO

ALTER DATABASE [Audit]

MODIFY FILE

( NAME = [Audit], -- Logical name you can find in step 1

FILENAME = 'G:\MSSQL_DATA\Audit.mdf'); -- New file path

To Move log files: Apply Step 3 & 4 for moving log files too.

Step 5: Bring database online.

USE master

GO

ALTER DATABASE Audit SET ONLINE;

Step 6: Perform consistency checks (CheckDB).

Connecting to SQL Server 2014 Integration Services failed with Error : "Access is denied"

I have installed SQL 2014 Database Engine with minimum requirement features and able to connect to SSMS successfully. Later we got a request to install SQL Integration Services. Here comes the problem, after the install when I connect to Integration services facing below error.

Connecting to the Integration Services service on the computer "localhost" failed with the following error: "Access is denied."

By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.

Resolution:

This is a UAC (User Access Control) issues. You need to run SSMS as administrator.

Start -> All Programs -> Microsoft SQL Server 2014 -> right click on SQL Server Management Studio 2014 -> click on Run as Administrator.

Database Log File Not Available Error: 9001

Jobs has been failed with error “The log for database 'AdventureWorksDW2008' is not available”

Ran DBCC CheckDB to Check logical and physical integrity of all the objects, Got below error.

Error: ‘The log for database xxxxx' is not available. Check the event log for related error messages. Resolve any errors and restart the database.’

Since the database log is in offline state, users won’t be able to perform the below activities on the database.

      1) Backup

      2) Integrity Check

      3) Any operation which requires logging into ldf file.

Resolution:

CAUSE: From the error log, state and severity details, it was found that the database log was taken offline. Usually this happens because of a catastrophic failure that requires the database to restart.

Most of the times I see this issue is caused due to database being closed. Just check is your database has auto_close enabled using below query.

select @@SERVERNAME AS server_name,getutcdate() as report_date_utc,name as database_name, is_auto_close_on , state_desc, user_access_desc

from sys.databases

where is_auto_close_on = 1

order by name asc

If auto_close is on, switch to off (Step 7).

This fix need taking database offline and make it online to recognize the log file. It can be done by taking database into Single User Mode which kills all connections to database.

In case you don’t have a good database backup, then export everything to a temporary database for rollback purpose.

Step 1: Create a temporary database with the name DBNAME_Temp and export all the data from affected database into the new database (DBNAME_Temp) using Export wizard.

Step 2: Script out User defined stored procedures, User defined functions, Triggers, and views, and users along with their permissions.

Step 3: Run the above scripts on the new database.

Step 4: Now bring the database offline using the command

Alter database [database_name] set offline with rollback immediate;

Go

Step 5: Copy .mdf and .ldf files of the affected database to a safe location.

Step 6: Bring the database ONLINE using the command.

Alter database [database_name] set online;

Go

Step 7:  Set auto_close option to Off

Alter database [database_name] set AUTO_CLOSE OFF;

Go

Step 8: Run DBCC CHECKDB on the database.

Note: Before performing above task make sure you have maintenance window on that database.

Cleanup pending mail queue

I see mail box is flooding with alerts, and many more in Queue to shoot our outlook mailbox.

First step is to stop the DBmail

Use MSDB

sysmail_stop_sp

This stored procedure Stops Database Mail by stopping the Service Broker objects that the external program uses.

Check the mail queue length with below query.

SELECT count (*)

FROM [msdb].[dbo].[ExternalMailQueue] WITH(NOLOCK)

Below query will clean up the external queue in service broker.

Use MSDB

declare @conversation uniqueidentifier

while exists (select 1 from dbo.ExternalMailQueue)

begin

set @conversation = (select top 1 conversation_handle from dbo.ExternalMailQueue)

end conversation @conversation with cleanup

end

Again check with mail queue length using query in step 2

Once the entire mail queue is cleared, please start the DBmail with below query.

Use MSDB

sysmail_start_sp

SQL Management studio failed to load.

    
      When you try to connect SQL studio this error prompts on your screen.

The 'Microsoft.SqlServer.Management.SqlStudio, Microsoft.SqlServer.Management.SqlStudio, Version=12.0.0.0, Culture=neutral,PublicKeyToken=89845dcd8080cc91' package did not load correctly.

Resolution:

Click Start
Search for SQL Server Management Studio
Right click on Management studio
Click on Run as Administrator

Move TempDB data and log files to new location

Step 1: Identify the current location of TempDB Data and Log Files.

Use master

GO

SELECT

name AS [LogicalName]

,physical_name AS [Location]

,state_desc AS [Status]

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

GO

Step 2: Change the location of TempDB Data and Log files using ALTER DATABASE.

Execute below query to change file path for tempdb data & log files.

Please make sure there are no active transactions on TempDB.

ALTER DATABASE Tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'T:\TempDB\tempdb.mdf');

GO

Note: If you have multiple data files perform above step for all those files.

ALTER DATABASE Tempdb

MODIFY FILE (NAME = templog, FILENAME = 'T:\TempDB\templog.ldf');

GO

Step 3: Restart MSSQL Services. There would be an outage of 5 minutes at maximum.

Check the job activity monitor for any active jobs running before restarting.

Step 4: Verify the File Change.

Check if new data files were as required on new path by below query.

Use master

GO

SELECT

name AS [LogicalName]

,physical_name AS [Location]

,state_desc AS [Status]

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

GO

Step 5: Go to the original path and delete all old data and log files for tempdb.

SQL Transactional Replication Error

Distribution agent failing with below error.

Error : The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.

To Troubleshoot this issue, just follow below steps.

Checked with inactive replication subscriptions by running below script on Publisher Distribution database.

Use Distribution

go

Select * From distribution..MSsubscriptions

Checked with status of the subscription. If status = 0 (Its Inactive). We need to make status = 2 (Active)

Executed below script to make subscription status = 2.

update distribution..MSsubscriptions set status=2

Next start the distribution agent and refresh the jobs to check the Distribution job status.