Sign Up Procedure for Interview Agent

Interview Agent

[caption id="attachment_616" align="aligncenter" width="600" caption="Call on +91-9881710142 for more information"][/caption]

SQLDBPool has now its own domain

Dear Readers,

Now you dont have to write the long http://sqldbpool.wordpress.com/ URL, We have now our own domain. You can now check your favourite articles by just typing http://sqldbpool.com/

Thank You

Best Regards,
Jugal Shah
jugal.shah@sqldbpool.com

Error: 1101, Severity: 17, State: 2.

Error: 1101, Severity: 17, State: 2.
Could not allocate a new page for database 'tempdb' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

We have configured the tempdb on J:\ which has only 10GB space. TempDB has occupied the 10GB space. We have tried to shrink the tempdb but no luck, after sometime we are not able to even right click the TempDB and neither execute the some system stored procedures (sp_who2)

Solution 1
If you don’t want to restart the SQL Server you can add the another data file to different drive in tempdb

ALTER DATABASE tempdb
ADD FILE
(
NAME = tempdev1,
FILENAME = 'E:\tempdb1.mdf',
SIZE = 5MB,
MAXSIZE = 5000MB,
FILEGROWTH = 10MB
) TO FILEGROUP [PRIMARY];
GO

Solution 2
If the disk space is available on the drive where tempdb residing you can change file MAX SIZE option to allocate more space.

Solution 3
Restart the SQL Services, it will create the new instance of tempdb

Solution 4
For user databases with restricted growth you can use the AUTO GROWTH option true or increase the file max size

Note: You can't move the tempdb files, if you execute the ALTER command to move the tempdb files, it will just mark the file to diffrent drive and will create the file the target drive on SQL Server restart only

How to move data and log file using Alter statement

You can use Alter command to move data and log file.

Sample Script

-- Moving data file to E:\ drive
ALTER DATABASE tempdb
MODIFY FILE (Name = tempdev,FILENAME = 'E:\tempdb.mdf')
-- Moving log file to E:\ drive
ALTER DATABASE tempdb
MODIFY FILE (Name = templog,FILENAME = 'E:\templog.ldf')


Shrink SQL Server 2000 Database

Query to generate DBCC shrinkfile script for all the user databases in SQL Server 2000


select 'use ' + ltrim(rtrim(db_name(sd.dbid))) + char(13) + 'dbcc shrinkfile (' + quotename(ltrim(rtrim(sf.name)),'''') + ' ,truncateonly)' from sysaltfiles sf
inner join sysdatabases sd on sf.dbid = sd.dbid
where sd.dbid > 4

Remote Desktop Error - The terminal server has exceeded the maximum number of allowed connections

You can visit my article on below link.

http://www.mssqltips.com/tip.asp?tip=1981

How to check authentication scheme in SQL 2005/SQL 2008?

You can use below query to check authentication scheme whether it is Kerberos or NTLM.

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

MVP Award

Dear Readers,

With your support and comments, I am announced as MVP
http://blogs.technet.com/southasiamvp/archive/2010/04/01/new-mvps-announced-april-2010.aspx



Thank You,
Jugal Shah