August 2017 Entries

Sql Server: How do I get the filegroup, data file name, size and path of a database?
-- start with this:SELECT dbfile.name AS DatabaseFileName, dbfile.size/128 AS FileSizeInMB, sysFG.name AS FileGroupName, dbfile.physical_name AS DatabaseFilePath FROM sys.database_files AS dbfile INNER JOIN sys.filegroups AS sysFG ON dbfile.data_space_id = sysFG.data_space_id-- for a more general look by filegroup, try this:with fileConfig as (SELECT dbfile.name AS DatabaseFileName, (dbfile.size/128) AS FileSizeInMB, sysFG.name AS FileGroupName, dbfile.physical_name AS DatabaseFilePath FROM sys.database_files ......

Posted On Tuesday, August 29, 2017 2:54 PM | Comments (0)

SQL Server: Why is it taking so long to take a database offline?
There are probably open sessions on the database you are attempting to bring offline. SQL Server is trying to roll back any existing workloads in-flight for that database. Issue the sp_who2 command from a new connection (master db) and view what's active. If you see activity, let it complete--or if you don't want the sessions to complete for whatever reason, issue the kill command for the spid(s). In the future, use this command:ALTER DATABASE yourDBName SET OFFLINE WITH ROLLBACK IMMEDIATE;To bring ......

Posted On Friday, August 11, 2017 8:21 PM | Comments (0)

SQL Server: Why is it taking so long to take a database offline?
There are probably open sessions on the database you are attempting to bring offline. SQL Server is trying to roll back any existing workloads in-flight for that database. Issue the sp_who2 command from a new connection (master db) and view what's active. If you see activity, let it complete--or if you don't want the sessions to complete for whatever reason, issue the kill command for the spid(s). In the future, use this command:ALTER DATABASE yourDBName SET OFFLINE WITH ROLLBACK IMMEDIATE;To bring ......

Posted On Friday, August 11, 2017 8:21 PM | Comments (0)

SQL Server: How can I get a distinct count(*) with multiple columns?
To get a count(*) of distinct column combinations, do the count(*) over the distinct select statement.Example:SELECT count(*) FROM (SELECT DISTINCT ColumnA, ColumnB, ColumnC FROM YourTable ) x ......

Posted On Wednesday, August 9, 2017 12:45 PM | Comments (0)