Misspelled Column Names in Live Databases

I’ve inherited another application, following the departure of the guy who used to support it.
It’s a daily data transfer process, and failry well-behaved. But users get twitchy if the transfer goes over time, so every now and then I need to check where it’s at in its run.
The application logs its process to a table. All very simple, except I shake my fist at it every time I go looking.

SELECT *
FROM ApplicationLog

Oops, cancel, cancel. The purge of data from this log is conservative, it keeps three months of data. So I want to see the most recent records.

SELECT *
FROM ApplicationStepLog
WHERE StepDate >= DATEADD(Day, -1, GETDATE())

Msg 207, Level 16, State 1, Line 3
Invalid column name ‘StepDate’.

Eh, what? Oh yeah. I keep forgetting. Stupid column is not called StepDate. It’s, umm, StepDDate. No. StepDatte. No. SteppDate. Two pees. Got it.

I went to the software manager and said I wanted to fix the spelling of the column name in the database.
My request was akin to asking him to rewrite some ancient scroll. The application was coded years ago, nobody left has ever amended it, he’s not even sure if the code is in source control. And if it is, is it up to date…
He enlists the Test Manager as an ally. Oh, the horror of regression testing a legacy application with no documentation.
Sensing my irritation, they promise me they’ll put the change request on the backlog.
Thanks, guys. So that’ll be never.

How big are all databases on this server?

DatabaseSizes

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?

last_date

 

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] = ‘U’
ORDER BY STATS_DATE(i.object_id, i.index_id) ASC

 

 

Sizes of all databases on a SQL Server instance

Neat little script to show the size of all databases on a server.

select db_name(database_id), physical_name, size,
(mf.size / 128) as size_MB,
(mf.size / 128)/1024 as size_GB
from sys.master_files mf
where type_desc = ‘ROWS’

 

List deadlock graphs, and the time of the deadlock

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 name = N’system_health’
AND target_name = N’ring_buffer’
) AS Data
— Split out the Event Nodes
CROSS APPLY TargetDataXML.nodes (‘RingBufferTarget/event[@name=”xml_deadlock_report”]’) AS XEventData (XEvent)
) AS tab (event_data)
order by 1 DESC

 

Show current Max Memory, Total Memory, and Available Memory

To get a quick overview of memory on your database servers:  MAX

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)) + ‘ MB ‘ [Available]
FROM sys.dm_os_sys_memory m
INNER JOIN sys.configurations c ON c.name = ‘max server memory (MB)’

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.

MAX

I calculate the appropriate setting for Max Memory using an old Glenn Berry post, with some modifications to the numbers based on experience with my own machines.

For new SQL Server installations, the install process launches a post-configuration script that sets the Max Memory based on the available windows memory.

USE [master]
GO

Declare @TotalMemoryMB INT, @MaxMemoryMB INT

SELECT @TotalMemoryMB = CAST(m.total_physical_memory_kb AS BIGINT) / 1024
FROM sys.dm_os_sys_memory m

IF @TotalMemoryMB = 32767 SET @MaxMemoryMB = 27000

ELSE IF @TotalMemoryMB = 16383 SET @MaxMemoryMB = 13500

ELSE IF @TotalMemoryMB = 8191 SET @MaxMemoryMB = 6400

ELSE IF @TotalMemoryMB = 4095 SET @MaxMemoryMB = 3200

ELSE print ‘Unusual RAM’

— Set max server memory limit
IF @MaxMemoryMB IS NOT NULL
BEGIN

print ‘Setting Memory to ‘ + Cast (@MaxMemoryMB as VARCHAR(20)) + ‘ MB’

EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE

EXEC sp_configure ‘max server memory (MB)’, @MaxMemoryMB

RECONFIGURE WITH OVERRIDE

END
go

 

How long will the Shrink take to complete?

Yeah, yeah, shrinking isn’t recommended. Except when you’ve run out of options.

skeleton_waiting

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 it hits 85 or 90% and then just sticks there as you re-run the script repeatedly. I will address remedies for this scenario in another post.

SELECT percent_complete,
start_time,
status,
command,
estimated_completion_time,
cpu_time,
total_elapsed_time
FROM sys.dm_exec_requests
WHERE command = ‘DbccFilesCompact’

 

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?sleeping_boy

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) AS [Percent Complete]
,CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time]
,CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min]
,CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min]
,CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours]
,CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r
WHERE command IN (‘RESTORE DATABASE’,’BACKUP DATABASE’)

 

 

Find the name of the file used in a database restore

Problem: There are five similarly named backups in the backup folder. Which file was used to restore the test database [Nero] ?

Confused

In case you’ve been looking fruitlessly, the restore file name is not in the properties of the database, nor is it in the [msdb] database (as of SQL Server 2008).

The Error Log is the place to go. Open the log, and filter on “Database was restored“.

The verbiage in the log will have the precise details of the restore: which database, and when and from which device. This is where you’ll see the specific file name(s).

Anyone with access can of course rename those files, and if they’ve done so you’re out of luck. Which may be why Microsoft doesn’t stick the source file name(s) from the particular time of the restore into the msdb database.