News

Copyright © 2008-2018 Paula DiTallo

Tag Cloud


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 ......

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 ......

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 ......

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 ......