Category Archives: SQL Server DBA Tips

How big are all databases on this server?

List the sizes of all databases on the server.   select db_name(database_id), physical_name, size, (size / 128) as size_MB, (size / 128)/1024 as size_GB from master.sys.master_files where type_desc = ‘ROWS’ order by db_name(database_id)      

When were index statistics last updated?

  When were statistics last updated on all indexes in the database?     SELECT o.name,  i.name AS IndexName, STATS_DATE(i.object_id, i.index_id) AS StatisticsDate FROM sys.objects o WITH (NOLOCK) INNER JOIN sys.indexes i WITH (NOLOCK) ON o.object_id = i.object_id INNER JOIN sys.stats s WITH (NOLOCK) ON i.object_id = s.object_id AND i.index_id = s.stats_id WHERE o.[type] =… Read More »

List deadlock graphs, and the time of the deadlock

Get a list of deadlocks, and their time of occurrence. Click on the XML to show the deadlock graph.   SELECT event_data.value(‘(/event/@timestamp)[1]’,’VARCHAR(50)’) AS TimeOfEvent, CAST(event_data.value(‘(event/data/value)[1]’, ‘nvarchar(max)’) AS XML) AS DeadlockGraph FROM ( SELECT XEvent.query(‘.’) AS event_data FROM ( SELECT CAST(target_data AS XML) AS TargetDataXML FROM sys.dm_xe_session_targets xest INNER JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address WHERE… Read More »

Show current Max Memory, Total Memory, and Available Memory

To get a quick overview of memory on your database servers:   SELECT @@SERVERNAME [Server] ,CASE WHEN c.value_in_use = 2147483647 Then ‘NO MAX’ ELSE CAST(c.value_in_use AS VARCHAR(20)) + ‘ MB’ END [Max Memory] ,CAST(( CAST(m.total_physical_memory_kb AS BIGINT) / 1024 ) AS VARCHAR(20)) + ‘ MB’ [Total] ,CAST(( CAST(m.available_physical_memory_kb AS BIGINT)/ 1024 ) AS VARCHAR(20)) +… Read More »

Setting Max Memory on new SQL Server installs

Our development and test database servers are in various sizes, depending on usage. We have 4 GB machines for sandbox environments, 48 GB machines for production-like usages such as load testing, and 16 and 32 GB environments for QA environments that don’t need the full production load. I calculate the appropriate setting for Max Memory using… Read More »

How long will the Shrink take to complete?

Yeah, yeah, shrinking isn’t recommended. Except when you’ve run out of options. Often, you’re shrinking that overgrown database when under pressure to free up space as quickly as possible, or at least inform a queue of developers/testers/managers as to when their problems will be resolved. This SQL provides an estimate. Unfortunately, there are times when… Read More »

How long will the database restore take to complete?

  Staring at the screen, waiting for the 5% progress line for that 100 GB restore? Is there a quick way to know how long the restore will take to complete? Here is the SQL for any restores and backups running on the server. It’s an estimate, but it’s usually pretty close.   SELECT r.session_id ,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)… Read More »

Find all tables with a foreign key to a specific column

Find all tables “foreign-keyed” to a column. Is that a legitimate verb? Perhaps not, but it’s often how the question is posed to me. Here is the script: SELECT REF.TABLE_NAME AS FK_TABLE_NAME ,REF.COLUMN_NAME AS FK_COLUMN_NAME ,REF.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE REF ON REF.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND REF.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND REF.CONSTRAINT_NAME =… Read More »