Backtrack:  
 
showing posts tagged with 'sql'
 
edited by on June 8th 2015, at 09:47
Maintaining indexes on your table is an important part of keeping your database healthy and its performance adequate. There are two maintenance operations for any index: rebuilding and reorganizing. Both operations were designed to get rid of index fragmentation, but differ in how this is done.

 RebuildReorganizeWhat it doesDrops the existing index and recreates it from scratch.Physically reorganizes the leaf nodes of the index.When to useIndex fragmentation >= 40%Index fragmentation >= 10% and < 40%Impact on systemHigh.

Database will be offline during process, unless you have Enterprise Edition and have enabled the ONLINE option. Online rebuild requires more resources than o  ...
edited by on May 26th 2015, at 14:17
You can move the tempdb of a SQL Server instance to another location using T-SQL, but this requires a little bit of downtime: setting a new location will only take effect after a restart of the SQL Server instance.

First, retrieve the tempdb's current location and logical name:

Use tempdbGOSELECT name,filename FROM sys.sysfilesGO

This should give you two entries: one for the actual database, and one for the transaction log of tempdb. Now that we have the location and logical name, we can change it:

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'T:\newdir\tempdb.mdf');GOALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'T:\newdir\templog.ldf');GO

Replace the p  ...
edited by on May 26th 2015, at 14:05

If, when attempting to start SQL Server instance, you get an error 1814, this means there's a problem with the tempdb database. Either it can't be created because the disk or volume is not accessible for writing (i.e. a security permission problem), or the volume on which the tempdb resides does not have enough space available. If the latter is the problem, you'll need at least 2 MB of free space for tempdb to be created.

edited by on May 22nd 2015, at 11:26
You can perform resource configuration for SQL Server Analysis Services (SSAS) through SQL Server Management Studio (SSMS). Simply connect to the SSAS (select Microsoft Analysis Server as server type and connect to the instance name). Using SSMS allows to change only a limited number of common and advanced parameters. Many of the very advanced parameters (usually those that require a restart of SSAS) can only be configured through a file called msmdsvr.ini.

The INI-file msmdsvr.ini, by default located in %PROGRAMFILES%\Microsoft SQL Server\<ssas-instance-name>\Config and in fact is an XML-file, allows you to configure both common and advanced parameters of SSAS. By default, the file i  ...
edited by on May 21st 2015, at 16:30
After running complex queries that use the tempdb of a SQL Server instance, it may become necessary to shrink the database again. However, when running the shrink operation on the tempdb, it can result in the database not shrinking at all, even when the used space is minimal.

The reason for this is most likely that the clearance of the tempdb is still in cache and not flushed to disk. You need to flush the changes to disk first, after which you will be able to shrink the tempdb. Do note that flushing will most likely impact database performance, so use with caution. Also, the tempdb will not shrink beyond the initial size, configured in the file group.

DBCC FREESYSTEMCACHE('ALL')USE [tempd  ...
edited by on May 20th 2015, at 16:18

You can retrieve a list of stored procedures in a SQL Server database through T-SQL by querying the built-in information_schema partition.

SELECT * 
FROM db_name.information_schema.routines 
WHERE routine_type = 'PROCEDURE'

Replace db_name with the name of the database you wish to retrieve the list of stored procedures. You can adjust the WHERE-clause even more to get a more narrow list.

You can also do this with the master database which will return all (system and non-system) stored procedures.

edited by on May 19th 2015, at 16:10

Probably MSSQL 101, but this is how to quickly retrieve the structure of a table:

EXEC sp_help tbl_name
GO

tbl_name is the name of the table.

edited by on August 26th 2014, at 12:13
If the Windows Internal Database, used by WSUS, and which is actually an embedded SQL Server, is eating all of your memory, you can limit its memory usage the same way you would with a real SQL Server.

Open an elevated command prompt and launch the SQL prompt:

For WSUS4 (on Server 2012):

osql -E -S \.pipeMicrosoft##WIDtsqlquery

For WSUS3 (on Server 2008, 2008R2 and SBS 2008/2011)

osql -E -S \.pipemssql$microsoft##sseesqlquery

This will log you in with the current credentials (which is why it has to be an elevated command prompt). The SQL prompt (1>) is shown.

Enter the following commands to initiate advanced configuration:

exec sp_configure 'show advanced option', '1';reconfigure;  ...
edited by on April 5th 2013, at 16:14

To change a user's (login's) password via Transact-SQL, use this:

ALTER LOGIN User WITH 
     PASSWORD = 'new-password' 
     OLD_PASSWORD = 'old-password';
GO
 
showing posts tagged with 'sql'
 
 
« December 2024»
SunMonTueWedThuFriSat
1234567
891011121314
15161718192021
22232425262728
293031    
 
Links
 
Quote
« If the batteries of a TV remote run out, why do we press the buttons so much harder? »