SQL

Identify Microsoft SQL Server version & edition

I wanted to determine which version of Microsoft SQL Server 2008 was running on one of my servers. So I opened up SQL Server Management Studio (SSMS), and ran the following command as a new query window:

SELECT SERVERPROPERTY (‘productversion’),
SERVERPROPERTY (‘productlevel’),
SERVERPROPERTY (‘edition’)

The following results are returned:
-The product version (for example, 10.0.1600.22)
-The product level (for example, RTM)
-The edition (for example, Standard Edition 64-bit)

Admin Access – SQL Server 2008 R2

Recently I was asked to assist someone with access to a Microsoft SQL 2008 R2 Server. Under normal conditions I would simple add them, but this isn’t the normal. I do not have access I need, so I will add myself so that I can add them.

Issue:
No one seems to have admin access to the SQL
The built-in sa account was not available (windows authentication mode)

Solution:
re-install SQL Server 

Better Solution:
Without the need to go into the basics I am going to assume you know your way around SQL Server and the server its hosted on.

Using the SQL Configuration Manager
1. Stop the instance of SQL
2. Stop all over SQL related services that might attempt to connect
3. Open a command prompt as administrator, and find your way to the SQL folder
( C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe” )
4. I then start SQL Server via command line with the -m parameter.  This runs SQL Server in single-user mode. Do not close the command prompt.

Using SQL Server Management Studio.
1. Now that you are in single user more use the SQL Server Management Studio (SSMS).
2. Next we add the local account or domain account you need. Remember to assigned them sysadmin roles.
3. When completed you can return to the previous command prompt and [CTRL] C and you will be asked if you would like to shutdown SQL. Type “Y” for yes. When SQL is offline, start SQL using the services console or SQL Configuration Manager.

It’s like magic isn’t it. I hope this helps.

–updated notes —

I ran into this issue also with SQL Express and while the same steps above apply we needed to also define the instance of SQL that was running. Here are the steps:

1. Shut down SQL Server from services
2. Open the command prompts (as admin) and run single-user mode as local admin with this command – “C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe” -m -s SQLEXPRESS
3. Go to the steps I provided under “Using SQL Server Management Studio”

VIM_VCDB database ‘PRIMARY’ filegroup is full

Additional Errors:  CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.

Cause:  The 4 GB limit is a limit of SQL Server Express 2005.   If you needed to grow your database beyond that size, you would want to upgrade to SQL Server 2005 Workgroup / Standard or Enterprise.  If you want to continue using SQL Express, then you’ll need to purge data from the database

 

Why does this happen, and how to prevent it

Virtual Center stores tasks, event, and performance data in the Virtual Center database. Over time, this data needs to be removed.  There are two variations of this solution, one for each of the supported vCenter database platform.

How to fix

– Make a full database backup

– Stop all Virtual Center Server services

Run the following script below on the Virtual Center Database

/*

VCDB_table_cleanup_MSSQL_V4.X.sql,v 4.0 2010/08/12

 

This script will delete data from designated tables in the VirtualCenter DB

for vc4.x versions.

 

You are strongly advised to shut down the VirtualCenter server and make

a complete backup of your database before running this script.

 

VirtualCenter Server must be stopped while this script is running.

 

Please see USER CONFIGURABLE PARAMETERS section below for options.

 

In particular, you must set @DELETE_DATA = 1 in order to actually delete rows;

this is a safety precaution.

 

Directions: open this file with one of the following and execute:

SQL Query Analyzer (SQL Server 2000) or

SQL Server Management Studio (SQL Server 2005)

SQL Server Management Studio (SQL Server 2008)

 

Connect using the same DB login that VirtualCenter uses.

 

The transaction log may fill up during this procedure if sufficient space

is not available.  Monitor the transaction log size and usage with this command:

 

dbcc sqlperf (logspace)

 

*/

 

 

IF OBJECT_ID(‘tempdb..#CLEANUP_VCDB’) IS NOT NULL

DROP TABLE #CLEANUP_VCDB

GO

 

SET NOCOUNT ON

 

DECLARE @VCUSER NVARCHAR(60)

DECLARE @VCUSERID INT

DECLARE @BATCH_SIZE INT

DECLARE @CUTOFF_DATE SMALLDATETIME

DECLARE @CUTOFF_DATE_S NVARCHAR(60)

DECLARE @DELETE_DATA BIT

DECLARE @CNT INT

DECLARE @TOT INT

DECLARE @SQL NVARCHAR(900)

DECLARE @FROM_VAL NVARCHAR(60)

DECLARE @WHERE_VAL NVARCHAR(900)

 

 

— ######### USER CONFIGURABLE PARAMETERS ########################

— 0 = COUNT ONLY; 1 = DELETE ROWS

SET @DELETE_DATA = 0

 

— Use one of these methods to specifiy the data cutoff date

SET @CUTOFF_DATE = GETUTCDATE()-180

–SET @CUTOFF_DATE = ‘2007/01/01’

 

— Number of rows to delete per transaction

SET @BATCH_SIZE = 10000

 

— ######### END USER CONFIGURABLE PARAMETERS ####################

 

— PERFORM SOME ERROR CHECKING TO ENSURE DB ACCESS

SELECT @VCUSERID = UID FROM SYSOBJECTS

WHERE NAME = ‘VPX_VERSION’

 

IF @@ROWCOUNT < 1

BEGIN

PRINT ‘The ‘ + DB_NAME() + ‘ database does not appear to contain VirtualCenter tables.’

PRINT ‘Please ensure you are connected to the correct database.’

RETURN — stop execution of this script

END

 

SELECT @VCUSER = NAME FROM SYSUSERS

WHERE UID = @VCUSERID

 

PRINT ‘VirtualCenter database user: ‘ + @VCUSER

PRINT ‘Logged in user: ‘ + CURRENT_USER

 

IF (@VCUSER <> CURRENT_USER) AND (@VCUSER <> ‘dbo’)

BEGIN

PRINT ‘***************************************************************’

PRINT ‘You do not appear to be logged in as the VirtualCenter DB user.’

PRINT ‘Please log in as ”’ + @VCUSER + ”’ to execute this SQL script.’

PRINT ‘***************************************************************’

RETURN

END

 

— DONE WITH ERROR CHECKING

 

— CONVERT THIS DATE TO A STRING WITH QUOTES FOR EFFICIENCY LATER

SET @CUTOFF_DATE_S = ”” + CONVERT(NVARCHAR, @CUTOFF_DATE, 111) + ””

PRINT ‘Cutoff date: ‘ + @CUTOFF_DATE_S

 

PRINT ‘Batch size: ‘ + CONVERT(NVARCHAR, @BATCH_SIZE)

 

PRINT CONVERT(NVARCHAR, getdate(), 120) + ‘ starting…’

 

CREATE TABLE #CLEANUP_VCDB (VPXTABLE NVARCHAR(40), CRITERIA NVARCHAR(250), INITIAL_CNT INT, DELETE_CNT INT)

 

IF ((SELECT VER_ID FROM VPX_VERSION)< 4)

BEGIN

INSERT #CLEANUP_VCDB VALUES (‘VPX_HIST_STAT’, ‘SAMPLE_ID IN (SELECT ID FROM VPX_SAMPLE WHERE SAMPLE_TIME < ‘ + @CUTOFF_DATE_S + ‘)’, 0, 0 )

INSERT #CLEANUP_VCDB VALUES (‘VPX_SAMPLE’, ‘SAMPLE_TIME < ‘ + @CUTOFF_DATE_S, 0, 0 )

END

ELSE

BEGIN

INSERT #CLEANUP_VCDB VALUES (‘VPX_HIST_STAT1’, ‘TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME1 WHERE SAMPLE_TIME < ‘ + @CUTOFF_DATE_S + ‘)’, 0, 0 )

INSERT #CLEANUP_VCDB VALUES (‘VPX_SAMPLE_TIME1’, ‘SAMPLE_TIME < ‘ + @CUTOFF_DATE_S, 0, 0 )

INSERT #CLEANUP_VCDB VALUES (‘VPX_HIST_STAT2’, ‘TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME2 WHERE SAMPLE_TIME < ‘ + @CUTOFF_DATE_S + ‘)’, 0, 0 )

INSERT #CLEANUP_VCDB VALUES (‘VPX_SAMPLE_TIME2’, ‘SAMPLE_TIME < ‘ + @CUTOFF_DATE_S, 0, 0 )

INSERT #CLEANUP_VCDB VALUES (‘VPX_HIST_STAT3’, ‘TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME3 WHERE SAMPLE_TIME < ‘ + @CUTOFF_DATE_S + ‘)’, 0, 0 )

INSERT #CLEANUP_VCDB VALUES (‘VPX_SAMPLE_TIME3’, ‘SAMPLE_TIME < ‘ + @CUTOFF_DATE_S, 0, 0 )

INSERT #CLEANUP_VCDB VALUES (‘VPX_HIST_STAT4’, ‘TIME_ID IN (SELECT TIME_ID FROM VPX_SAMPLE_TIME4 WHERE SAMPLE_TIME < ‘ + @CUTOFF_DATE_S + ‘)’, 0, 0 )

INSERT #CLEANUP_VCDB VALUES (‘VPX_SAMPLE_TIME4’, ‘SAMPLE_TIME < ‘ + @CUTOFF_DATE_S, 0, 0 )

END

 

DECLARE curse CURSOR FOR

SELECT VPXTABLE, CRITERIA FROM #CLEANUP_VCDB

 

OPEN curse

FETCH NEXT FROM curse INTO @FROM_VAL, @WHERE_VAL

 

WHILE (@@FETCH_STATUS = 0)

BEGIN

SET @SQL = ‘SELECT @CNT= COUNT(1) FROM ‘ + @FROM_VAL + ‘ WHERE ‘ + @WHERE_VAL

— this is required to get result into a variable

EXEC sp_executesql @SQL, N’@CNT INT OUTPUT’, @CNT = @CNT OUTPUT

 

SET @SQL = ‘UPDATE #CLEANUP_VCDB SET INITIAL_CNT = ‘ + CONVERT(NVARCHAR, @CNT) + ‘ WHERE CURRENT OF curse ‘

EXEC(@SQL)

 

IF (@CNT = 0)

BEGIN

PRINT CONVERT(NVARCHAR, getdate(), 120) + ‘ ‘ + @FROM_VAL + ‘: no matching rows to delete.’

FETCH NEXT FROM curse INTO @FROM_VAL, @WHERE_VAL

CONTINUE

END

 

PRINT CONVERT(NVARCHAR, getdate(), 120) + ‘ ‘ + @FROM_VAL + ‘: will attempt to delete ‘ + CONVERT(NVARCHAR, @CNT) + ‘ rows.’

 

IF @DELETE_DATA = 1

BEGIN

SET @CNT = 0

SET @TOT = 0

 

SET ROWCOUNT @BATCH_SIZE

 

WHILE 1=1

BEGIN

BEGIN TRAN

SET @SQL = ‘DELETE FROM ‘ + @FROM_VAL + ‘ WHERE ‘ + @WHERE_VAL

EXEC(@SQL)

 

SET @CNT = @@ROWCOUNT

SET @TOT = @TOT + @CNT

 

COMMIT TRAN

 

IF @CNT < @BATCH_SIZE BREAK

PRINT CONVERT(NVARCHAR, getdate(), 120) + ‘ completed ‘ + CONVERT(nvarchar, @TOT) + ‘ rows…’

END –ROW BATCH LOOP

 

SET ROWCOUNT 0

PRINT CONVERT(NVARCHAR, getdate(), 120) + ‘ ‘ + @FROM_VAL + ‘: deleted ‘ + CONVERT(nvarchar, @TOT) + ‘ total rows.’

 

SET @SQL = ‘UPDATE #CLEANUP_VCDB SET DELETE_CNT = ‘ + CONVERT(NVARCHAR, @TOT) + ‘ WHERE CURRENT OF curse ‘

EXEC(@SQL)

 

END — DELETE DATA SECTION

ELSE

BEGIN

PRINT CONVERT(NVARCHAR, getdate(), 120) + ‘ This is a test run, no data was deleted.’

END

 

FETCH NEXT FROM curse INTO @FROM_VAL, @WHERE_VAL

END — END CURSOR LOOP

 

CLOSE curse

DEALLOCATE curse

 

IF @DELETE_DATA = 1

BEGIN

PRINT ‘ ‘

PRINT ‘****************** SUMMARY *******************’

 

DECLARE curse CURSOR FOR

SELECT VPXTABLE, INITIAL_CNT, DELETE_CNT FROM #CLEANUP_VCDB

 

DECLARE @INITIAL_VAL INT, @DELETE_VAL INT

 

OPEN curse

FETCH NEXT FROM curse INTO @FROM_VAL, @INITIAL_VAL, @DELETE_VAL

 

WHILE (@@FETCH_STATUS = 0)

BEGIN

IF (@INITIAL_VAL <> @DELETE_VAL)

BEGIN

PRINT ‘Potential problem: attempted to delete ‘ + CONVERT(NVARCHAR, @INITIAL_VAL)

+ ‘ rows, but only ‘ + CONVERT(NVARCHAR, @DELETE_VAL) + ‘ deleted.’

END

ELSE

BEGIN

PRINT ‘Deleted ‘ + CONVERT(NVARCHAR, @DELETE_VAL)

+ ‘ rows from ‘ + CONVERT(NVARCHAR, @FROM_VAL) + ‘ table.’

END

 

FETCH NEXT FROM curse INTO @FROM_VAL, @INITIAL_VAL, @DELETE_VAL

END

 

CLOSE curse

DEALLOCATE curse

 

END –DELETE DATA CHECK

 

DROP TABLE #CLEANUP_VCDB

By |How-To, Technical|Comments Off on VIM_VCDB database ‘PRIMARY’ filegroup is full