Developer's Closet A place where I can put my PHP, SQL, Perl, JavaScript, and VBScript code.

19Apr/160
Manage Traces in SQL Server

Quick notes on managing traces in SQL Server:

View all running traces:

SELECT * FROM sys.traces

Disable and delete the trace with the @status option = 2:

EXEC sp_trace_setstatus @traceid = 2 , @status = 0
EXEC sp_trace_setstatus @traceid = 2 , @status = 2

Use option 0 = disable, 1 = start, 2 = disable and delete.

Filed under: SQL No Comments
9Feb/150
Find and Delete Running SQL Trace

Yesterday we had a long running trace take up too much space on disk. To find the running trace, look for the location of the file to identify your trace, and get the id:

SELECT * FROM sys.traces

Next, disable and delete the trace with the @status option = 2:

EXEC sp_trace_setstatus @traceid = # , @status = 2

Use option 0 = disable, 1 = start, 2 = disable and delete.

Have fun with traces, but make sure to stop them before you go home for the weekend!

Filed under: SQL No Comments
7Jun/140
SQL Query for Recently Changed Objects

These may be useful to determine when objects have been changed.  We might infer from this info when was the last formal deployment of a database.

 

-- get most recently changed object of each type

SELECT  o.name

,SCHEMA_NAME(o.schema_id) AS 'SchemaName'

,COALESCE(OBJECT_NAME(o.parent_object_id), '') AS 'ParentName'

,o.type

,o.type_desc

,o.create_date

,o.modify_date

FROM    sys.objects o

JOIN(SELECT    type

,'LateDate' = MAX(modify_date)

FROM      sys.objects

WHERE     is_ms_shipped = 0

AND OBJECT_SCHEMA_NAME(object_id) <> 'tSQLt'

GROUP BY  type

) xo

ON o.type = xo.type

AND o.modify_date = xo.LateDate

ORDER BY o.modify_date DESC

,o.type

,o.name

 

-- get 20 most recently changed objects

SELECT TOP 20

o.name

,SCHEMA_NAME(o.schema_id) AS 'SchemaName'

,COALESCE(OBJECT_NAME(o.parent_object_id), '') AS 'ParentName'

,o.type

,o.type_desc

,o.create_date

,o.modify_date

FROM   sys.objects o

WHERE   o.is_ms_shipped = 0

AND o.parent_object_id = 0

--AND o.type = 'U'

ORDER BY o.modify_date DESC

 

-- get 20 most recently changed objects that are not tSQLt or tSQLt tests

SELECT TOP 20

SCHEMA_NAME(o.schema_id) AS 'SchemaName'

,o.name

,COALESCE(OBJECT_NAME(o.parent_object_id), '') AS 'ParentName'

,o.type

,o.type_desc

,o.create_date

,o.modify_date

FROM   sys.objects o

WHERE   o.is_ms_shipped = 0

AND o.parent_object_id = 0

--AND o.type = 'U'

AND OBJECT_SCHEMA_NAME(o.object_id) NOT LIKE 'Z%'

AND OBJECT_SCHEMA_NAME(o.object_id) <> 'tSQLt'

ORDER BY o.modify_date DESC

 

Filed under: SQL, Windows No Comments
30May/140
Fix an SQL Database Marked Suspect

If you have a database marked suspect, and do not have a backup to restore from, you can attempt to repair the database. This may lead to data loss and is most certainly not the best option (see: database backup).

Make sure there are no open connections to the database - stop all services that might attempt a connection. Run sp_who2 to check on open connections.

Run the commands one at a time and fix any errors as they appear:

EXEC sp_resetstatus 'DatabaseName';
ALTER DATABASE DatabaseName SET EMERGENCY;
DBCC CHECKDB ('DatabaseName');
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ('DatabaseName', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE DatabaseName SET MULTI_USER;

Restoring from a backup is the better way to fix this issue.

Filed under: SQL, Windows No Comments
22May/140
Purge Backup History from SQL msdb Database

I just about ran out of space on my Data partition and discovered that the msdb database was over 114 GB! In particular, the backupfile table had over 107,000,000 rows which took up over 70 GB. Other backup-related tables took up the rest of the space. This is a real problem. The normal command I would run to purge backup history did not work because it uses transactions and would fill up TLog. So I had to run the sp in a look one day at a time:
SET NOCOUNT ON
DECLARE @purge_date DATETIME
DECLARE @cutoff_date DATETIME

-- Set purge cutoff to n days back
set @cutoff_date = DATEADD(dd,DATEDIFF(dd,0,GETDATE())-180,0)

PRINT 'Start Date: ' +
CONVERT(VARCHAR(10),GETDATE(),121)
PRINT 'Purge backup history before Cutoff Date: ' +
CONVERT(VARCHAR(10),@cutoff_date,121)

WHILE 1 = 1
BEGIN

SET @purge_date = null

-- Find date of oldest backup set
SELECT @purge_date = DATEADD(dd,DATEDIFF(dd,0,MIN(backup_finish_date))+1,0) FROM msdb.dbo.backupset(NOLOCK)
WHERE backup_finish_date <= @cutoff_date

IF @purge_date is null or @purge_date > @cutoff_date
BEGIN
PRINT 'Purge backup history complete through: '+
CONVERT(VARCHAR(10),@cutoff_date ,121)
BREAK
END

PRINT CHAR(10)+CHAR(13) +
'Purging backup history before: ' +
CONVERT(VARCHAR(10),@purge_date,121) +CHAR(10)+CHAR(13)

SELECT [Backup Sets to be Deleted Count ] = COUNT(*) FROM msdb.dbo.backupset(NOLOCK)
WHERE backup_finish_date < @purge_date

EXEC msdb.dbo.sp_delete_backuphistory @purge_date

END

PRINT 'End Date: ' +
CONVERT(VARCHAR(10),GETDATE(),121)

 

Filed under: SQL, Windows No Comments
10Feb/140
Query for the Size of All Databases in SQL

If you need to see the size of all databases on an SQL server, here is a helpful query, one I always seem to need to write by memory:

SELECT d.name AS Name,
(SUM(mf.size) * 8 / 1024 / 1024) AS Size_GB
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- skip system databases
GROUP BY d.name
ORDER BY d.name

 

Filed under: SQL, Windows No Comments
13Aug/130
SQL 2008 R2: Cannot Bring Database Back Online

I had a terribly stressful evening at work last night. I brought a database offline to move a file and when I tried to bring the database back online I received the following error:

Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'DatabaseName', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Nothing I tried worked and a Google search was not helpful. Here is what happened:

I made a mistake right from the beginning, I should have moved the file before I set the database offline, but that is not what caused the problem:

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE DatabaseName SET OFFLINE;

-- copy the file to the new location...

ALTER DATABASE DatabaseName MODIFY FILE
(
Name = FileName,
Filename = 'F:\New\Location\Name.mdf'
);

ALTER DATABASE DatabaseName SET ONLINE;

ALTER DATABASE DatabaseName SET MULTI_USER;

-- delete old file...

As soon as I tried to set the database online, I received the error message: User does not have permission to alter database 'DatabaseName', the database does not exist, or the database is not in a state that allows access checks.

I did not think this was a permissions problem. At first I chased the part of the error that the database is not in a state that allows access checks. This led me nowhere because it makes sense that the database was not in a state that allows access checks because it has been set offline.

So a coworker looked at the file I moved. He discovered that file did not inherit permissions from the parent folder and the permissions were wrong. As soon as I reset permissions on the file I was able to set the database back online.

This was a frustrating problem, but I learned something I can add to my toolbox. Here is what I will do in the future:

ALTER DATABASE DatabaseName MODIFY FILE 
(
Name = FileName,
Filename = 'F:\New\Location\Name.mdf'
);

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE DatabaseName SET OFFLINE;

-- copy the file to the new location...

-- reset permissions on the file...

ALTER DATABASE DatabaseName SET ONLINE;

ALTER DATABASE DatabaseName SET MULTI_USER;

-- delete old file...

Filed under: SQL, Windows No Comments
11Apr/13Off
Move Data to Another SQL Server in Batches

I ran into a problem where I was unable to move data from one database to another in one INSERT INTO statement. I used the statement below to cut up the data into more manageable batches. This script allowed me to reliably move 5 million rows:

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempHolding]') AND type in (N'U'))

DR0P TABLE TempHolding
SELECT Id INTO TempHolding
FROM Databasename.Schema.TableName

WHILE EXISTS(SELECT TOP 1 1 FROM TempHolding)
BEGIN
SELECT TOP 50000 Id INTO TempStaging FROM TempHolding
INSERT INTO DestinationDatabaseName.Schema.TableName
SELECT t.* FROM Databasename.Schema.TableName t
JOIN TempStaging m ON m.Id = t.Id

DELETE j FROM TempHolding j WHERE EXISTS (SELECT j FROM TempStaging p WHERE p.Id = j.Id)
DR0P TABLE TempStaging

END

DR0P TABLE TempHolding

Note: I had to change DR0P table into DR0P table because of a filter on my text editor.

Filed under: SQL, Windows Comments Off
20Mar/130
Filter results from sp_who2

It can be very helpful to filter the results of sp_who2 in SQL 2008:

DECLARE @sp_who2_temp_table TABLE(
        SPID INT,
        Status VARCHAR(100),
        LOGIN VARCHAR(150),
        HostName VARCHAR(100),
        BlkBy VARCHAR(100),
        DBName VARCHAR(100),
        Command VARCHAR(250),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(100),
        ProgramName VARCHAR(254),
        SPID_1 INT,
        REQUESTID INT
)

INSERT INTO @sp_who2_temp_table EXEC sp_who2

SELECT * FROM @sp_who2_temp_table
WHERE DBName = 'DatabaseName'
 

Study the blocking query:

-- Enter SPID
DBCC INPUTBUFFER(77)
 

Enjoy the results!

Filed under: SQL, Windows No Comments
21Feb/130
Query for All Database Backups Within the Last Week

I was asked to retrieve a list of all database backups within the last week. Unfortunately, I forgot about the backupset table within msdb. However, Tim Ford posted a great blog article on the backupset table and included a few examples. After a few modifications, here is what I used:

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
(CONVERT(VARCHAR, CONVERT(DECIMAL(18, 2), msdb.dbo.backupset.backup_size/1024/1024), 1)) AS backup_size_in_mb,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date

Filed under: SQL, Windows No Comments