Once you have connected to MS SQL using SSMS Right click on the database Select New Query Paste the below query and click Execute SELECT t .NAME AS TableName, s .Name AS SchemaName, p .rows AS RowCounts, SUM(a .total_pages) * 8 AS TotalSpaceKB, SUM(a .used_pages) * 8 AS UsedSpaceKB, (SUM( a.total_pages ) – SUM( a.used_pages […]
Step 1: Go to https://sqlbackup.appliedi.net/ and choose the server which your database is held on: Step 2: Now type in your username (login) and password of your database user and click Connect: Step 3: go to the Tools section and click Restore databases: Step 4: Now […]
You can connect to a SQL Server 2005/2008 database using SQL Server Management Studio Express Edition. (this download is available free at http://www.microsoft.com/en-us/download/details.aspx?id=22985) Step 1: Open up SQL Server Management Server Studio and click “Registered Servers” Step 2: Right click “Database Engine’, go to “New”, then click “Server Registration” […]
Summary When you restore a Microsoft SQL Server database on a different machine, you cannot access the database until you fix the permissions. Detail The problem is that the user in the database is an "orphan". This means that there is no login id or password associated with the user. All of these instructions […]
Step 1: Go to https://sqlbackup.appliedi.net/ and choose the server which your database is held on: Step 2: Now type in your username (login) and password of your database user and click Connect: Step 3: Now look at the left side in the Tools section and click Backup databases: […]
By default SQL Express does not accept any remote connections. You won’t be able to connect to it with SQL Management Studio, or an ODBC connection for example until you enable it to accept connections. If you plan to do all SQL management while connecting to the server with Remote Desktop, then leave the SQL TCP/IP option […]
When you perform any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. When changes are made to the data that affect the index, index fragmentation can occur and the information in the index can get scattered in the database. Fragmented data can cause SQL Server to perform unnecessary data […]
You can query the sysobjects system table or the INFORMATION_SCHEMA views by running the following stored procedure: 1 EXEC sp_tables This will output all the objects in your database. <!– –>
The following code will display User Tables by SIZE in DESCENDING ORDER: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 […]
This script returns the main properties for all data files and log files in the current database including logical and physical file names, size, used space, and file growth parameter. 1 2 3 4 5 6 7 8 9 SELECT fileid, sf.groupid, grp=LEFT([groupname],20), lname=LEFT([name],20), size_mb=[size]/128 ,used_mb=FILEPROPERTY([name], ‘SpaceUsed’)/128 ,file_growth=case when (sf.status&0x100000) > 0 then str(growth)+’ […]