I'm trying to determine how much space I would need for my data drive and log file drive to do index rebuild. I have a database which is 100gb, it is in simple recovery mode. let me know what to have a look at to determine how much space.
View 7 RepliesI have 10 Gb index and disk space only left 5gb .
How can i rebuild index ?
I am using SQL Server 2008 (RTM) Standard Edition.
In my environment, one of my Database size is 75 gb and I have to create a plan for index rebuild using maintenance plan.But when we rebuild indexes, it requires some space on data and log files of database.how can we calculate disk space requirement for index rebuild process ?
We face slow performance issue for like taking long time for same query execution after We apply index rebuild and reorganize index. But, after execution of query or procedure for 2 -3 times, performance will be faster. I have following questions
1 do we need to update stats after we rebuild an reorganize index.
2. is it will be slow for 1-2 times for every query and stored procedure execution after we rebuild and reorganize index?
I've been fixing some issues lately where weekly maintenance has been causing logs to grow and filling disks.
Is there any rule of thumb for allocating log space for doing reorgs and rebuilds in a worst case scenario? I'm thinking 3x the largest database size?
I've been watching them run on databases in the range of 50GB where the logs are growing well over that for rebuilds or even reorgs. Once you have a few databases like this on a server, you can suddenly eat through a lot of disk space just for holding logs during maintenance.
I had to reinstall my local copy of SQL a few weeks ago, which naturally overwrote the
msdb.dbo.sysmanagement_shared_server_groups_internal and
msdb.dbo.sysmanagement_shared_registered_servers_internal tables.
However I still have the local XML file that SSMS reads so I can still access the groups, I just get weird errors when trying to re-register my install as the new CMS. How to rebuilt those tables from the XML file or know of a way to repopulate?
Do I need to rebuild my indexes on my High Availability listeners?
When I do a full index rebuild on my primary DB's. Does rebuilding also send the rebuild to the listener(s)?
i have add maintenance plan rebuild index in sql server 2014 log file increased very tremendous space.
View 6 Replies View RelatedI have question about tempdb needs to be configured 100GB 64kb block size.its fresh installation.
how to allocate the file sizes.still im not sure how many log files needs to be created with 100GB equals to 64KB block size.
what is 64KB block size and how to divide the logfiles 64KB into the 100gb or 50GB?
what is 64 KB cluster has 128 sectors?
tempdb drives should be formatted with a 64K allocation? how many files needs to created for good performance with 50GB or 100GB? ot 1TB
We have deleted 120GB of data but space did not released even after 2 days. Is there any reason for this? tell me how exactly it releases the space after truncating a 120GB table?
View 8 Replies View RelatedWhile i execute dbcc sqlperf(logspace); I get following values.
Database NameLog Size (MB)Log Space Used (%)
master 16.17969 13.30275
tempdb 7.429688 61.7245
model 0.7421875 45.78947
msdb 5.554688 25.87904
distribution 2808.93 0.8172179
BANKDB 23438.87 48.20037
WSMIRSDB 109.7422 4.839111
For database BANKDB , Log Space used(%) is 48.83% and Log size is about 23438.87 where as my database size of BANKDB is 60 GB. FULL database and Log back is done every day night one time. My database is performing slow now.
Do we need to take log backup frequently like once a 1 hour so that Log space used will be less. Same query is taking more time to execute than before in same database is it because of log file has increased.
I do index organize and rebuild once a week and stats apply nightly.
Is it correct once log space size is increasing more than 10%. Do we need to take log backup?
How do i find Total allocated space and used space of a memory optimized filegroup?
use memory_optimized_db
Go
select (SUM(size)*8.0)/1024.0 as Space,
FILEGROUP_NAME ( data_space_id ) , type_desc from sys.database_files
group by data_space_id,type_desc;
above query gives "current used size of the container " of memory optimized file group but doesn't give Total space detail.
I'm upgrading to SQL 2012 from 2008R2, while doing so i will be rebuilding all the indexes on all the database. In my previous environment while doing so, i got space related error in primary filegroup for insufficient space in the primary filegroup. Is there any rule of thumb about how much space is required by index rebuild command for each database, or is there a safe threshold for free space in the database?
View 9 Replies View RelatedDead lock is coming in select query in application because of index. It is identified after enabling trace in database and identified by reading deadlock xml file. After index removal, deadlock is not coming in same query. But it is affecting query's performance slightly. Is it correct way to remove index if dead lock is coming because of index?
View 3 Replies View RelatedWe have a database with a table that contains around 180m records. Each day a further 70k are inserted. No records are ever deleted as this table is used for archiving only.Users are required to perform SELECTs on this table constantly but due to the high number of INSERTs the indexes become very fragmented very quickly. My aim is to avoid daily rebuilds of the indexes which is what our software house is telling us we have to do.
This is the DDL for the table:
CREATE TABLE [dbo].[Inventory](
[EAN] [bigint] NOT NULL,
[Day] [smalldatetime] NOT NULL,
[State] [int] NOT NULL,
[Quantity] [int] NULL,
[StockValue] [float] NULL,
CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED
[code]...
There are also three clustered Indexes on this table each referencing a single column. The problem from my side is that I cannot understand why the three columns in a primary key would also be configured as non-clustered indexes.My solution would be one of the following:
1. Accept the tables are going to be fragmented and require a daily rebuild (don't like this one!)
2. Partition the table
3. Remove the non-clustered Indexes and let the clustered index for the primary key do the work.
I would like to put a Clustered Index on a date column in a current heap, but one question/concern.This heap every month has thousands of rows deleted and even more added later. How much of an issue will this cause the Clustered Index as far as page splits? I was thinking Fill Factor of 70%.I would normally just test and still will on Dev box, but my Dev box is much smaller than production as far as power.
View 6 Replies View RelatedI created columnstore index on the table with 20 columns and about 1000 000 000 rows
every day added about 5M rows
"select" queries became faster because of batch mode and table demand less disk space then before
I have also 6 similar tables with 5 000 000 000 rows and plan to move them on columnstore index
server has 128 G RAM
What pitfalls I could face if I will have so many columnstore indexes on one server?
How a could see problems in DMV?
I have a situation where I need to rebuild indexes on a large DB (500G).
When I do a test run of the rebuilds in my test environment it uses 100G of space - which is fine with me.
When I do a rebuild in my High Availability environment - same DB, same script - it eats up over 600G of space and fills the volume.
What can I do without removing my DB from H/A to rebuild the indexes?
I want to create a lot of index for my database for performance.
But I need find memory usage by indexes.
How to find memory usage by index in sql server?
We use SQL server always on feather on my database and we distribute statement on main database server and mirror database server for raise performance.
My police for split statement is DML (insert, update and delete) statement go to main DB and Read Data (select) statement go to mirror DB.
I want know can I use different index on main DB and mirror Database?
Because some index are used in mirror DB not used in main database.
I am running a DB with 250Gb of documents, the fulltext index just keeps growing and growing. The files in the MssearchCatalogDir folder is currently taking up 106Gb, it was only 74Gb this morning. The full text catalog size property says its only 53.6Gb and this is remaining steady while the files in the MssearchCatalogDir folder seem to be balloning out of control. I ran a reorg on the fulltext catalog and it did not alter the file space (actually increased it).
There is one *.ci file that is doing most of the growing, its about three times as big as the second biggest one and is expanding before my eyes.
Should I have turned population off when I did the reorg?
Manamgement studio has an Optimize catalog option on the fulltext catalog properties dialog, is this different from a fulltext catalog reorg? Should I run this.
Should I run a shrink file on the filegroup containing the fulltext catalog (the filegroup itself is very small, all the space is in the MssearchCatalogDir folder)?
I have 140Gb left on this drive, is it just going to keep on expanding until I'm out of room, I just dont know what I should do.
Abit more info.....
I was rebuilding the index from scratch (needed to move it to another filegroup).
When I first started the rebuild, msftesql.exe and msftefd.exe were both taking alot of cpu and the overall cpu usage was high. Now after 12 hours, only msftesql.exe is running and is taking up 5-10% cpu. Could it be that the rebuild is not complete yet? Although the Item Count property of the full text catalog does seem to indicate that all documents have been processed, the processes keeps running and disk space keeps going down.
I need to restore a SQL 2008 db on SQL 2014 instance with out upgrading the database(changing compatibility level).
This database has full text enabled. I see one full text catalog an 2 full text indexes.
Do I need to worry about anything or can I perform a clean restore from the backup with full text import?
Do I need to rebuild the catalog in this case?
I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.
I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.
I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).
So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.
-- Initialize Control Mechanism
DECLARE@Drive TINYINT,
@SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area
DECLARE@Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @Drive <= 122
BEGIN
SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives
(
Info
)
EXEC(@SQL)
UPDATE@Drives
SETDrive = CHAR(@Drive)
WHEREDrive IS NULL
SET@Drive = @Drive + 1
END
-- Show the expected output
SELECTDrive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM(
SELECTDrive,
Info
FROM@Drives
WHEREInfo LIKE 'Total # of %'
) AS d
GROUP BYDrive
ORDER BYDrive
E 12°55'05.25"
N 56°04'39.16"
Hi,
I have a 250 GB database and not much space left on the disk drive. I want to run SQLMAINT to do optimization and integrity checks on this db. My question is : How much work space does SQLMAINT need to perform these tasks?.
Thanks in advance for your help.
F.
How to increase disk-space of database in sql server?
View 4 Replies View Related
How do we identify the total disk space of a SQL Server Remotely. The procedure xp_fixeddrives gives the available free space .. but I would like to know the total disk capacity .. Any pointers?
Thanks,
Deepti
As a normal sql user we are not able to get the disk space information by
executing 'xp_fixeddrive' extended stored procedure.
We are able to get the result using sa user and windows authenticated user.
Any help will be appreciated.
I would be able to determine how much hard disk space is available on the Server with a SQL Query, and from an other computer on the LAN.
How can I do something?
Thanks for your help.
My SSIS package is running very slow taking so much time to execute, One task is taking 2hr for inserting 100k records, i have disabled unused index still it is taking time.I am rebuilding/Refreshing indexes and stats once in month if i try to execute on daily basis will it improve my SSIS Package performance?
View 2 Replies View RelatedMy SQL server disk space is getting close to full capacity which is causing certain reports that we run via the SQL server to time out because I don't think there is enough space on the server.
Any tips on cleaning out a SQL server? Are there any folders that can absolutely be deleted to clear space? I know on a local computer that the %temp% folder can be cleaned out. I know when dealing with servers you do not want to make to many changes because it can cause major problems down the road.
Hi,
The SQL Server Database hangs overnight and also consumes high disk space on one of our servers. This has been recurring for quite a few weeks and occurs daily.
Can somebody assist me in trouble-shooting the same
Thanks in Advance
Deepak
We had one of the disks in our array fail yesterday morning. The rebuild process using the hot spare went into effect as one would expect. My question is: during this rebuild process, could additional errors be introduced into the database? I'm asking because the current production db has more errors than the restored database from last night.
Msg 8928, Level 16, State 1, Line 3
Object ID 210099789, index ID 255: Page (1:128895) could not be processed. See other errors for details.
Msg 8939, Level 16, State 106, Line 3
Table error: Object ID 210099789, index ID 255, page (1:128895). Test (m_freeCnt == freeCnt) failed. Values are 0 and 8094.
Msg 8939, Level 16, State 108, Line 3
Table error: Object ID 210099789, index ID 255, page (1:128895). Test (emptySlotCnt == 0) failed. Values are 1 and 0.
Msg 8928, Level 16, State 1, Line 3
Object ID 210099789, index ID 255: Page (1:469839) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 3
Table error: Object ID 210099789, index ID 255, page (1:469839). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
Msg 8928, Level 16, State 1, Line 3
Object ID 210099789, index ID 255: Page (1:469935) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 3
Table error: Object ID 210099789, index ID 255, page (1:469935). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
Msg 8928, Level 16, State 1, Line 3
Object ID 210099789, index ID 255: Page (1:491535) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 3
Table error: Object ID 210099789, index ID 255, page (1:491535). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
Msg 8928, Level 16, State 1, Line 3
Object ID 210099789, index ID 255: Page (1:680703) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 3
Table error: Object ID 210099789, index ID 255, page (1:680703). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
Msg 8928, Level 16, State 1, Line 3
Object ID 210099789, index ID 255: Page (1:762783) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 3
Table error: Object ID 210099789, index ID 255, page (1:762783). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
CHECKDB found 0 allocation errors and 13 consistency errors in table 'session_state' (object ID 210099789).
Msg 8928, Level 16, State 1, Line 3
Object ID 234028165, index ID 0: Page (1:14175) could not be processed. See other errors for details.
Msg 8944, Level 16, State 17, Line 3
Table error: Object ID 234028165, index ID 0, page (1:14175), row 0. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 326 and 300.
Msg 8928, Level 16, State 1, Line 3
Object ID 234028165, index ID 0: Page (1:155583) could not be processed. See other errors for details.
Msg 8944, Level 16, State 12, Line 3
Table error: Object ID 234028165, index ID 0, page (1:155583), row 1. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 3840 and 388.
Msg 8928, Level 16, State 1, Line 3
Object ID 234028165, index ID 0: Page (1:179199) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 3
Table error: Object ID 234028165, index ID 0, page (1:179199). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
Msg 8928, Level 16, State 1, Line 3
Object ID 234028165, index ID 0: Page (1:237663) could not be processed. See other errors for details.
Msg 8944, Level 16, State 12, Line 3
Table error: Object ID 234028165, index ID 0, page (1:237663), row 0. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 91 and 26.
Msg 8928, Level 16, State 1, Line 3
Object ID 234028165, index ID 0: Page (1:1060304) could not be processed. See other errors for details.
Msg 8944, Level 16, State 12, Line 3
Table error: Object ID 234028165, index ID 0, page (1:1060304), row 1. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 61455 and 397.
Msg 8928, Level 16, State 1, Line 3
Object ID 234028165, index ID 0: Page (1:1392799) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 3
Table error: Object ID 234028165, index ID 0, page (1:1392799). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
CHECKDB found 0 allocation errors and 12 consistency errors in table 'clientCampaignActivityAll' (object ID 234028165).