Skip to content

Posts from the ‘Database’ Category

Apr 25

Query to find the size of each database in Netezza environment

Please run below query to find the size of each database in Netezza environment. The output of this query will be the database name and total space allocated to the database.
This data is stored in system views under system database. 

SELECT ORX.database::nvarchar(64) AS "DatabaseName",
case when sum(SOD.allocated_bytes) is null then 0 else SUM(SOD.allocated_bytes)/1073741824 end AS "AllocatedSpace_GB"
FROM _V_SYS_OBJECT_DSLICE_INFO SOD INNER JOIN _V_OBJ_RELATION_XDB ORX ON ORX.objid = SOD.tblid
GROUP BY "DatabaseName"
ORDER BY "DatabaseName"

Now if you want to know the free space available to the database as well then try below query:

SELECT ORX.database::nvarchar(64) AS "DatabaseName" ,
case when sum(SOD.used_bytes) is null then 0 else SUM(SOD.used_bytes)/1073741824 end AS "UseSpace_GB",
case when sum(SOD.allocated_bytes) is null then 0 else SUM(SOD.allocated_bytes)/1073741824 end AS "AllocatedSpace_GB",
current_timestamp as "CurrentTime"
FROM _V_SYS_OBJECT_DSLICE_INFO SOD INNER JOIN _V_OBJ_RELATION_XDB ORX ON ORX.objid = SOD.tblid
GROUP BY "CurrentTime", "DatabaseName"
ORDER BY "DatabaseName"

Apr 12

Auditing database restore in SQL Server instance

How to confirm if someone have restored a database? Which database backup file is restored? WHo did the restore? When was the database last restored?

To find the answer of all these questions you may have to run couple of queries shown below.
Please make sure you run these queries under MSDB database.

1) Below query will give us a details of which all databases has ben restored, when these were restored, who restored it and the backup set which was restored.

select restore_date,destination_database_name,user_name,
backup_set_id, restore_type, recovery
 from restorehistory

OUTPUT:

restore_date		destination_database_name	user_name	backup_set_id	restore_type	recovery

2012-08-07 13:35:29.013	TestDB_RC1		TestID_8181	358			D	1	1
2012-08-07 16:39:49.370	TestDB_RC1		TestID_5357	358			D	1	0
2012-08-07 16:41:19.680	TestDB_RC1		TestID_5357	372			I	0	1

D – Full database backup applied
I – DIfferential backup applied

2) Now to find when was these backups taken, just run the below query. Hre you may have to copy the desired backup set number from the first query.
NOTE: If no result is displayed then possibly the backup which was restored is not from this instance. That mean a backup was taken in some other environment and then restored in current.

 select backup_set_id,name, backup_start_date,backup_finish_date, 
 type, database_name from backupset
 where backup_set_id = 358 or backup_set_id = 372

OUTPUT:

backup_set_id	name						backup_start_date	backup_finish_date	type	database_name

358		TestDB_backup_2012_07_07_133002_2720281	2012-07-07 13:30:08.000	2012-07-07 13:30:22.000	D	TestDB
372		TestDB_backup_2012_07_08_230001_5563963	2012-07-08 23:00:17.000	2012-07-08 23:00:19.000	I	TestDB

3) Additionally you can look into the SQL Server error log to find when was database restored and the location where database backup file was placed for this restore.

2012-08-07 16:39:59.44 spid64      The database 'TestDB_RC1' is marked RESTORING and is in a state that does not allow recovery to be run.
2012-08-07 16:39:59.90 Backup      Database was restored: Database: TestDB_RC1, creation date(time): 2012/05/14(12:51:48), first LSN: 11037:12735:86, last LSN: 11037:12771:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'TestServer\MSSQLSERVER\TestDB\TestDB_backup_2012_07_07_133002_2720281.bak'}). Informational message. No user action required.
2012-08-07 16:41:22.15 spid51      Starting up database 'TestDB_RC1'.
2012-08-07 16:41:22.15 spid51      The database 'TestDB_RC1' is marked RESTORING and is in a state that does not allow recovery to be run.
2012-08-07 16:41:22.68 spid51      Starting up database 'TestDB_RC1'.
2012-08-07 16:41:23.16 spid51      CHECKDB for database 'TestDB_RC1' finished without errors on 2012-07-07 11:00:16.783 (local time). This is an informational message only; no user action is required.
2012-08-07 16:41:23.16 Backup      Restore is complete on database 'TestDB_RC1'.  The database is now available.
2012-08-07 16:41:23.18 Backup      Database changes were restored. Database: TestDB_RC1, creation date(time): 2012/05/14(12:51:48), first LSN: 11040:10549:56, last LSN: 11040:10573:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'TestServer\MSSQLSERVER\TestDB\TestDB_backup_2012_07_08_230001_5563963.diff'}). This is an informational message. No user action is required.

Sep 6

Who changed SQL Server database state to OFFLINE?

To check, who changed the SQL Server database status to OFFLINE, please follow the below steps.

1) Connect to SQL Server → open SQL Server logs and scan through the logs. You will find entry like:
Date 9/6/2012 10:54:54 AM
Log SQL Server (Current – 9/1/2012 12:30:00 PM)
Source spid61
Message
Setting database option OFFLINE to ON for database test21.

This mean, test21 database was changed to OFFLINE on 9/6/2012 @ 10:54:54 AM . Also, we know this change was done by spid 61 at that time.

2) Now go to Windows event viewer (under Administrative Tools) and open Application logs. Here try to look for entries by Source = MSSQLSERVER around 9/6/2012 10:54:54 AM timeframe (This step will be done by logging in to the server by making remote desktop connection)

If windows authenticated account changed the database state then you will find that account here against User. If the change was made by sql authenticated account then it will show N/A.

Proceed to next step if it says N/A.

3) Now we know the spid, timeframe when this change was made. Also, we know the change was made by sql authenticated account. Now, run the below script by changing spid value and starttime to correct value.

DECLARE @FileName VARCHAR(MAX)

SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
 FROM sys.traces
 WHERE is_default = 1;

SELECT DatabaseID, HostName, ApplicationName, LoginName, DatabaseName
 FROM sys.fn_trace_gettable( @FileName, DEFAULT )
 where starttime = '2012-09-06 10:54:55.117'
 and spid=61

Output:-

DatabaseID HostName ApplicationName LoginName DatabaseName
23 NHYUJM987123456 Microsoft SQL Server Management Studio DM\USER123 test21
23 NHYUJM987123456 Microsoft SQL Server Management Studio DM\USER123 NULL

From output you can see that this change was made by use DM\USER123.

Please note, this step will work only if default trace is enabled and the trace file is still available for the timeframe when database status was changed.

Additionally, you can follow above steps to check who changed database state to ONLINE, READ-ONLY, etc.

May 25

How to change Netezza database to read only mode?

Netezza have not documented any procedure to change database in read only mode. But we do have a commands to change database to read only and then change back to read write.

To change the database to read only mode you can run below command in SYSTEM database. Only the admin user or any user with alter privileges on a database can run below commands.

alter database TestDB lock for update;

This commnad will immediately lock TestDB to read only mode. All user sessions (other than user which ran the command) will get error messages if they try to do any modifications to this DB. This will only allow the current user session (user which ran this command) to do any modificatins to this database. Once this session is closed, then no one can make any changes untill we unlock the database for changes.

To verify if your database is in read only mode or not, you can run below query and look for DBLOCKTYPE value as ‘U’

select DATNAME, DBLOCKPID, DBLOCKTYPE from _T_DATABASE

Now to change this DB back to read write mode, run below command in system database.

alter database TestDB unlock;

These scripts will run good for TwinFin systems with Netezza version 6 plus. Have not tested on Netezza version below 6.

Feb 6

Error: 9002, Severity: 17, State: 4

Error

Error: 9002, Severity: 17, State: 4.

The transaction log for database ‘MyDB’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

CAUSE

Your database properties is set to auto-shrink and log file is set to some restricted value.

You will hit this error when you are trying to shrink/auto_shrink starts for database log file but it didn’t work because of some active transactions. Please note, active part of the log file cannot be shrunk and can cause delay in shrinking process. In order to identify the exact reason as what is causing this issue, run below select query: -

select log_reuse_wait,log_reuse_wait_desc from sys.databases where name like 'MyDB'

If log_reuse_wait is ’0′ that mean you are good to shrink the file now. You can see the short description of each log_reuse_wait value against the log_reuse_wait_desc field. For detailed on log_reuse_wait values visit here.

RESOLUTION

Here your database needs more log space so increases the log space or set the maximum limit of log file to some higher value/unlimited (make sure auto growth of database log is is enabled). Also, it is recommended to disable auto_shrink as keeping it enabled is not a good option.