I have a table that contains 1.6 million rows, and is about 2.6GB in size.One of the columns is ntext and contained some xml.Realistically, I don't need the xml for anything older than about 2 months, however, i'd like to keep some of the data in the other fields.I decided that if I updated the xml to blank, I would see some considerable space savings, however that doesn't appear to be the case.This is the output of sp_spaceused for my table
name rows reserved data index unused
CommitmentsForPosting1660979 2740336 KB 1857104 KB312 KB882920 KB
After I updated the table to remove the xml, the output of sp_spaceused remained the same.My first thought was that it was probably statistics, so I updated statistics for the table, and nothing changed.I then updated statistics for everything in the database, still no change.I then ran DBCC CLEANTABLE for that table. I didn't really expect it to make a difference, and no surprise, it didn't.
The index on the table is a clustered index on just a GUID column.I rebuilt the index and again it still made no difference (that's a bit of a lie - index_size changed by a few hundred KB)my next test was to run 'select * into XXX from YYY' to create a copy of this table with the same data and data types. I also created the same clustered index from the original table onto the new one.If I then run sp_spaceused on this new copy of the table, I see what I expect to see in sp_spaceused - a table using approximately 256MB of space
name rows reserved data index unused
SPS_TEST 1660979 266400 KB 266400 KB 304 KB56 KB
To be honest, this isn't hugely critical, but I'm just curious as to why the original table is still reporting 2.6GB size, when I think it should probably be nearer 256MB.
can anyone help me? I just installed WinNT 4 SP3 and now trying to install SQL 6.5 on an IBM Netfinity 5500 with RAID 5. There is one 500mb partition for the system and then one 12 gig partition and SQL only seems to see ~117mb of the 12 gig partition. Is this a known prob? Thanks for any input Josh
I have deleted nearly 30 million rows from a table. But however when I used the sp_spaceused command to calculate the data occupied by the table I don't see any difference in the data size of the table. In fact the data has increased to few MBs after the deletion, but not much.
We have an application with replicated environment setup on sql server 2012 . Users will have a replica on their machines and they will replicate to the master database. It has 3 subscriptions subscribed to the publications on the master db.
1) We set up a replica(which uses sql server 2012) on a machine with no sql server on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 33gigs and ldf has grown to 41 gigs. I went to sql server management studion . Right click and checked the properties of the local database. over all size is around 84 gb with little empty free space available.
2) We set up a replica(which uses sql server 2012) on a machine with sql server 2008 on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
3) We set up a replica(which uses sql server 2012) on a machine with sql server 2012 on it. We have dropped the local database and recreated the local db and did the initial synchronization using replmerge tool. The mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
Why it is allocating the space differently? This is effecting our initial replica set up times.
I'm looking for monitoring page file usage for SQL Server. Ultimately, I'm trying to prove that there is not a problem with a server and that it is not struggling with the workload, but someone has looked at page file usage and suggested there is a problem.
I've set up performance counters running from a separate server, but the counters relating to page file usage seem wildly inaccurate. E.g., I've got (_Total\% Usage), and (Total)\%Usage Peak), with Total Usage showing a constant value of 64, and the peak showing 92. I've also got Process (sqlservr)Page File Bytes and Page File Bytes Peak, which are showing values like 61,275,688,960 and hardly dropping below this.
If I look at Performance Monitor directly the values shown in here correlate with this, as shown in my first screenshot. However, if I look at resource monitor and look at disk activity, you can see that there is basically nothing going on (screenshot 2).
The server is more than equipped to deal with the workload, and looking at other counters for SQL there is no indication of problems, e.g. page life expectancy has a minimum value showing of 576,258, there is never less than 6GB of free RAM, SQL compilations are at a maximum of 5% of batch requests.
It is a physical server with 2x 8 core multi threaded CPUs, 64GB RAM with 58GB as the SQL server maximum. Hopefully this is enough info, but I'm happy to check anything else.
Is the SQL Server Profiler Reads Column Incorrect For Parallel Plans?
I often use profiler as one tool to identify bad plans. The reads column gives me a good indication of excessive IO to dig into and correct if necessary. I often use it with Showplan so I can see what a query does, replicate it and fix it.
However I have just lost some faith in it. I am looking at a poorly performing query joining five tables. A parallel plan has been generated and one table is being scanned (in parallel) due to a missing index. This table had in excess of 4 million rows in it. The rest hitd indexes well. However the entire query generates ONLY 12 READS.
Once corrected, a single processor plan is used. This looks really efficient and uses 120 reads. That looks the right figure to me.
Does the profiler only display one thread of a parallel plan perhaps? Or something else?
Hi, i use this script that show me the size of each table and do the sum of all the table size.
SELECT X.[name], REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows], REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved], REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data], REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size], REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused] FROM (SELECT CAST(object_name(id) AS varchar(50)) AS [name], SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows], SUM(CONVERT(bigint, reserved)) * 8 AS reserved, SUM(CONVERT(bigint, dpages)) * 8 AS data, SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size, SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused FROM sysindexes WITH (NOLOCK) WHERE sysindexes.indid IN (0, 1, 255) AND sysindexes.id > 100 AND object_name(sysindexes.id) <> 'dtproperties' GROUP BY sysindexes.id WITH ROLLUP) AS X ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup. example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
I actually ran this on a reporting copy of my DB (restored once a week). I would guess the errors are present in the main copy as well. I fear running the check during production hours as it takes 2.5 hours and the system (4 processor) is under a lot of load for almost the entire day and much of the night.
It looks like the repair with data loss is my only choice but I would definitely prefer to know which rows are messed up. I'm also wary of running the repair as it's probably going to take well over an hour on the huge sessions table. I don't know how it got in this corrupted state. I inherited a DB of this size (50GB) that's been known to be very sensitive to any admin or index fiddling due to the high load. After I get this repaired I feel I should run the CHECKDB in a maintenance plan but I'm not sure if this will cause slowdowns in itself if it runs for 2.5 hours. The DB backup takes over 2 hours leaving almost no spare time in the off peak time.
Any advice/suggestions? Thanks.
DBCC results for 'dbloc'.
DBCC results for 'sessions'. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 462624691, index ID 0: Page (1:9517861) could not be processed. See other errors for details. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 462624691, index ID 0: Page (1:9659261) could not be processed. See other errors for details. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 462624691, index ID 7: Page (1:5825743) could not be processed. See other errors for details. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 462624691, index ID 7: Page (1:5890660) could not be processed. See other errors for details. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 462624691, index ID 7: Page (1:5931145) could not be processed. See other errors for details. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 462624691, index ID 7: Page (1:5950021) could not be processed. See other errors for details. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 462624691, index ID 7: Page (1:5978420) could not be processed. See other errors for details. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 462624691, index ID 7: Page (1:5988261) could not be processed. See other errors for details. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 462624691, index ID 7: Page (1:6046306) could not be processed. See other errors for details. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 109 and 110. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 112 and 113. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 113 and 114. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 114 and 115. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 117 and 118. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 118 and 119. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 121 and 122. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 122 and 123. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 124 and 125. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 510624862, index ID 0: Page (1:11858027) could not be processed. See other errors for details. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 510624862, index ID 0, page (1:11858027). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 510624862, index ID 0: Page (1:11865547) could not be processed. See other errors for details. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 510624862, index ID 0, page (1:11865547). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 510624862, index ID 2: Page (1:11889147) could not be processed. See other errors for details. DBCC results for 'sessions'. There are 153364096 rows in 3102681 pages for object 'sessions'. CHECKDB found 0 allocation errors and 9 consistency errors in table 'sessions' (object ID 462624691). DBCC results for 'map'. Server: Msg 8939, Level 16, State 98, Line 1 Table error: Object ID 510624862, index ID 2, page (1:11889147). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 510624862, Index ID 3. Keys out of order on page (1:11931268), slots 206 and 207. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 510624862, Index ID 3. Keys out of order on page (1:11931268), slots 209 and 210. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 510624862, Index ID 3. Keys out of order on page (1:11931268), slots 210 and 211. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 510624862, Index ID 3. Keys out of order on page (1:11931268), slots 230 and 231. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 510624862, index ID 11: Page (1:11988308) could not be processed. See other errors for details. Server: Msg 8944, Level 16, State 1, Line 1 Table error: Object ID 510624862, index ID 11, page (1:11988308), row 194. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 7251 and 18. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 510624862, index ID 11: Page (1:12003992) could not be processed. See other errors for details. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 510624862, index ID 11, page (1:12003992). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1. There are 15576829 rows in 65455 pages for object 'map'. CHECKDB found 0 allocation errors and 23 consistency errors in table 'map' (object ID 510624862). Server: Msg 8928, Level 16, State 1, Line 1 Object ID 1790629422, index ID 16: Page (1:11471717) could not be processed. See other errors for details. Server: Msg 8944, Level 16, State 1, Line 1 Table error: Object ID 1790629422, index ID 16, page (1:11471717), row 88. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 3500 and 28. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 1790629422, index ID 16: Page (1:11474112) could not be processed. See other errors for details. Server: Msg 8944, Level 16, State 1, Line 1 Table error: Object ID 1790629422, index ID 16, page (1:11474112), row 108. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 745 and 20. DBCC results for 'links'. There are 9919777 rows in 124284 pages for object 'links'. CHECKDB found 0 allocation errors and 4 consistency errors in table 'links' (object ID 1790629422). CHECKDB found 0 allocation errors and 36 consistency errors in database 'dbloc'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (dbloc ). DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After upgrading a server from SQL Server 2000 to 2005, an index was suddenly being reported as corrupt in the SQL Server log (probably every time an Insert was done). DBCC CHECKDB / CHECKTABLE reported no errors, with or without the new WITH DATA_PURITY option.
Anyone else experienced something similar?
How can an index be reported as corrupt when DBCC doesn't report it?
Dropping and re-creating the index solved the problem. I've restored a backup of the database (made before dropping/re-creating the index) to try repeating the problem, but no success so far.
Hi, I have SQL Server 2005 Developer Edition installed on my laptop. At this time, our servers here are currently running SQL Server 2000 Enterprise edition. When I try to use the DTS import/export wizard to transfer records between two SQL Server 2000 databases, SQL 2005 always reports the transfer as successful! Although when you check the destination table there is only ever 1 record transfered
This has happened every I have attempted, on any tables, are there any fixes to either perfmit the copy of all records or at least report the error condition that prevents records being transfered?
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
I was having interesting discussion on estimation of log file with a fellow collegue who happens to be quite knowledgable as well.
He told me if we identify the most frequently hit tables for a database and then (sum their sizes * 1.5) for OLAP we get rough estimate for disk space to be allocated for log file.
I am working on Sql Server 2012. and I have multiple databases there. Out of those, i want to move one of my databases to other SQL server 2012, For that i was trying to get approximate size of my database on current server. As i don't have the admin rights, so i can't get that. Can i get the approximate size by right clicking on database and by using the size property Under Database category to get the size idea?
Using BULK Insert with a format file I am receiving the following message:
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'STREAMS' reported an error. The provider did not give any information about the error. The statement has been terminated.
I am running SQL Server 7.0 w/ SP1 applied. The same data file and format files work fine if I use bcp.
I have a report that is sent via email to a list of recipients. When I look at the subscription in 'My Subscriptions' the value in the Status column states that the message was 'sent successfully', however the email does not appear in the inboxes of any of the intended recipents. The report renders OK via the browser and only takes a few seconds to process. The subscription exports the report as an Excel file. The excel file is approx 8MB in size but this is still below the attachment size limit for internal emails within my organisation. The subscription is tied to a shared schedule. Other reports that are tied to the shared schedule are shown as successful in 'My Subscriptions' and also appear in the inboxes of recipients. All email address are correct.
I forgot to mention, the report was successfully received by all recipients until very recently and the report has not been modified since this time.
I receive an error message in event log when i try to connect to the Database Server using ODBC on a client machine. The database server is running on Windows 2003 Server Standard Edition and the client machine is Windows XP Professional. Following is the error message from the event log:
2147467259 - [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it.
What causes the error to occur and how to resolve it?Appreciate for your assistence.
Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "VFPOLEDB" for linked server "tele" reported an error. Access denied. Msg 7301, Level 16, State 2, Line 1 Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "VFPOLEDB" for linked server "tele".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "tele2" returned message "The Microsoft Jet database engine could not find the object 'prospect_divu'. Make sure the object exists and that you spell its name and the path name correctly.". Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_COLUMNS" for OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "tele2". The provider supports the interface, but returns a failure code when it is used.
Hi all, I faced a problem, I have two tables - part and partmaster part : part_no, part_qty (no key) partmaster : part_no, part_description (primary key : part_no )
I want to select table part.* and partmaster.part_description.
(run on mssql 2k) select a.*, b.part_description from part a, partmaster b where a.part_no *= b.part_no
I want to and expect to have the result order like table "part". However, after the join, the order is different. I try to run it on mssql 7.0, the order is ok.
Then I modify and run the statement select a.* from part a, partmaster b where a.part_no *= b.part_no on 2k again. The result order is ok.
can anyone tell me the reason?
Now I try to fix this problem is adding a sequence field "part_seq" into table "part" and run the statement by adding a order by part_seq. It does work!
finding the database size from the backup file.I have SQL 2012 backup file, is there any way to find the estimated database size from the backup.I tried restoring , i got an error saying " no space need additional xxx bytes " ...does this error gives the exact space needed to restore ?
One more question....one of the backup file size is 7.2 GB, when i try to restore it ....it throws error saying it needs 292GB extra space while only 100 Gb is available. How come 392 Gb sized database becomes 7.2 Gb .bak file ?
