SQL Server 2008 :: Table Size After Reindex

Feb 22, 2015

I'm trying to understand what is happening to one large table.

In a DB SQL 2008R2, I'm trying to track a rapidly increasing DB size. It's due to one table recently added.

Despite the table's no rows increasing its size reduces after a scheduled re-index.

I've recorded the space used by this table by recording EXEC sp_spaceused 'tableName'

---
NoRows reserved data index_size unused
128864512300384 KB 2290928 KB9432 KB24 KB AFTER reindex
128864515406232 KB 5366184 KB39280 KB768 KBBEFORE reindex

N.B. The only thing I'm aware of happening in the time period is a reindex as part of scheduled task. I could be missing something else happening.

The table has only one Index the PK which is clustered, No Fill factor is specified. The Server Default fill factor =0.

I read fillfactor=0=100 Will always try and fill the pages so space used will be minimised?

After running the reindex the index fragmentation is v.low. I've not recorded the fragmentation before reindex.

I can see the Data is not added in Clustered index order.

View 2 Replies


ADVERTISEMENT

SQL Server 2008 :: Alter Column And Reindex 100M Row Table?

Jun 3, 2015

I inherited a system which has an index on a set of columns which allow more than 900 bytes of data in it. We know one of the fields can be shortened to shrink the potential key size below 900 bytes.

The problem is the table is about 120m rows, and the index currently on that column is seeked (sought?) on about 2.5m times a day.

At its simplest, I want to drop the existing index, alter the column to shrink the varchar size, and then rebuild the index on the newly shortened column.

On a smaller, less used table, I might just do this in outside of business hours and call it a day, but I'm concerned that this will take a long time and block a lot of operations.

1) IIRC, shrinking a column, unlike widening it, is much more expensive, even if there are no values which would actually end up trunacted. Is this right?

2) I did a few tests on some other smaller (2+ m) row tables and was still able to select data out of the table. I don't think this covered all the read scenarios, but are there known scenarios which would simply not work during an index build?

3) I haven't yet tried DML operations to the table while it's doing either the column update or an index build. what scenarios would or would not be blocked?

View 0 Replies View Related

SQL Server 2008 :: Estimate Size Of A Table

Feb 4, 2015

I have a table like below,

CREATE TABLE Student
(
Id BIGINT not null
,Name NCHAR(20) not Null
,Branch NVARCHAR (64) null
)

The table contains : 100000 rows .

1)Number of rows in a data page
2)Total number of pages required for the table
3)Total Table size in KB or MB
4)Total file size in Kb or MB

View 4 Replies View Related

SQL Server 2008 :: Way To Check To Find Number Of Rows And Size Of A Table

Apr 29, 2015

How can we monitor the all tables in all databases and send notifications to the team.Is there a way to check to find the no of rows and size of a table last month and find out growth % now

View 4 Replies View Related

SQL Server Admin 2014 :: Possible To Find Table Size And In That Table Each Row Size

Jun 10, 2014

It is possible to find table size and in that table each row size.

View 4 Replies View Related

SQL Server 2008 :: Data Type And Size Of Each Row

Feb 27, 2015

In my database there is a big table and format is something like this:

CREATE TABLE [dbo].[MyTable](
[aaa] [uniqueidentifier] NULL,
[bbb] [uniqueidentifier] NULL,
[ccc] [nvarchar](max) NULL,
[ddd] [nvarchar](100) NULL,.......etc.........

There are some more columns with more 'nvarchar' (max) and other INT data types. Anyway, I know a page is 8K size. How do I find out how much space does A ROW takes with above datatypes? If users add 5000 Rows per day, how do I figure out how much size the table will increase?

View 9 Replies View Related

SQL Server 2008 :: How To Reduce MDF File Size

Apr 30, 2015

I have issue with my DATA file ( MDF) .. The usage is 99.87% for database DB1 . File size is 3 GB . How do I reduce it ?

I have tried to shrink it by changing the recovery from FULL to SIMPLE and set it back to FULL .

I notice the index Defragmentation is high ..

Can I change the Initial Size into 1 GB for example ?

View 9 Replies View Related

SQL Server 2008 :: TempDB Size Hovering Around 45%

Jul 27, 2015

My prod server (only default instance) is configured TempDB 1024 MB data and Log 200MB. when I run 'sqlperf logspace' it shows most of time around 45% 'log space used'. There nothing going on the instance when I ran 'whoisactive' and select * from sys.sysprocesses where dbid = 2!!!

So my questions are is this normal to see log space around 45%, how to find what what CAUSED the tempdb log space to grow 45%? Is there something to do about it?

View 6 Replies View Related

SQL Server 2008 :: View Log Cache Size

Jun 21, 2010

Can I view the log cache size in SQL Server memory any DMV's which states that.

View 9 Replies View Related

SQL Server 2008 :: Temp DB Size Values From Different Tables

May 25, 2015

For finding size values of temp database:

select * from sys.master_files -
size column value here is 1024 for .mdf,size here for .ldf is 64
select * from tempdb.sys.database_files -
size column value here is 3576 for .mdf,size here for .ldf is 224

Why is there a difference and not the same. size columns in the above 2 tables for temp db's do they represent different values ?

View 1 Replies View Related

SQL Server 2008 :: Size Limitation For XML Data Type?

Oct 7, 2015

I've never worked with the XML data type in SQL Server, although I know its been there for a few iterations of SQL SErver. Now I've got a situation in which it might store some configuration data as XML, since that's the way it comes. (We had thought about storing the data in a VARCHAR(MAX) field.)

The first question is does the XML data type have a size limitation? For example do you do something like:

ConfigFile XML(1000) NULL

Or is it just something like this:

ConfigFile XML NULL

The second question is persisting the data to a file. As the name I choose for the variable suggests, we want to save the data from a configuration file into a SQL Server database. How do we go about doing that? We'll be developing a C# application, it will read and write the data both from the SQL table and the user's local HD.

View 5 Replies View Related

SQL Server 2008 :: Replication Error - Field Size Too Large

Feb 1, 2011

I've got two databases on the same server and replicate some tables from one database to another.The replication is configured so not to drop the table if it exists, but to delete the data based on the filter if one exists. There are two tables on the subscriber that have some extra columns.

I get "field size too large" error when trying to replicate them. Is there a workaround without having to make the publisher and the subscriber tables identical by schema?

View 5 Replies View Related

SQL Server 2008 :: Initial Size For TempDB Data And Log File?

Sep 12, 2011

We have installed SQL Server 2008 R2 SP1 instance and it's having Share Point 2010 databases.

We have 2 dedicated drives for Tempdb on SAN with 50 GB space. Both tempdb data & log files are created with default size. I would like to presize them.

What are the best values to start with?

U ->Tempdbdata having tempdb.mdf file
V->Tempdblog having templog.ldf file

View 9 Replies View Related

SQL Server 2008 :: Finding Backup File Size In Advance?

Mar 8, 2015

My backups are failing sometimes.My db size is around 400 GB and we are taking backup to the remote server. Free size available on the disk is showing 600 GB but my database full backup run more than 10 hrs and failed. The failure reason is there is not enough space on the disk.

What could be the possible failure reasons? It has more than the the database size on the backup server but why it is showed that msg on the job failures. I noticed same thing occurred couple of times in the past.

Is there any way to find how much the backup file will be generate before we run the backup job?

i.e. If we run the full backup of test1 database now, it will generate ....bak file for that test1 db

currently we are using maintenance plan and with compression (2008R2) and the database has TDE enabled

View 3 Replies View Related

SQL Server 2008 :: Virtual Log Files And Determining Right Growth Size

Oct 14, 2015

Any good starting point to understand for a specific db, how many max VLFs are good to have so that it does not cause long startup or backup times?

Also, I need some calculation so that I can identify a best growth parameter I will setup for each database ?

I'm seeing the below msg in errorlog and curious to know the changes (right sizing/growth) to be done? As of now 100 MB of log file growth value is set (refer: [URL] ....)

Database BizTalkMsgBoxDb has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

View 3 Replies View Related

SQL Server 2008 :: DBmail HTML Body Size Limit

Nov 4, 2015

When sending an email in HTML format, shouldn't this allow for 2gb of data? Mine is getting truncated after 4000 characters.

@body NVARCHAR(MAX) = NULL,

EXEC msdb.dbo.sp_send_dbmail
@recipients='someone@some.com',
@reply_to='someone'
@from_address='someone@here.com>',
@profile_name = 'profilename',
@body_format = 'HTML',
@body = 'lots of data'

View 2 Replies View Related

SQL Server 2008 :: Identify Size Of Data Cache Allocated From Buffer Pool

Oct 20, 2011

How do i check the size of the datacache allocated from the buffer pool by sql server?

DMV or anything to show me the pool allocation sizes for the various pools in sql server i think i may be able to work from there.

View 9 Replies View Related

SQL Server 2008 :: Insert From Table 1 To Table 2 Only If Record Doesn't Exist In Table 2?

Jul 24, 2015

I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query

INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)

[Code] ....

View 4 Replies View Related

SQL Server 2008 :: Capture Sessions Which Modify A Table With Details Modified In Table?

Apr 10, 2015

I created am inventory table with few columns say, Servername, version, patching details, etc

I want a tracking of the table.

Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.

View 1 Replies View Related

SQL Server 2008 :: Inserting Data From Staging Table To Main Table

Feb 23, 2015

I am trying to insert bulk data into main table from staging table in sql server 2012. If any error comes, this total activity is rollbacked. I don't want that to happen. I want to know the records where ever the problem persists, and the rest has to be inserted.

View 2 Replies View Related

SQL Server 2008 :: Create A Table Valued Function That Fetch Through The Table?

Apr 24, 2015

I would like to create a table valued function that fetch through the table below using a cursor and return the records that are unique

EmpidChDateSiteuseridinitsal finsalNote
-------------------------------------------- ----------
236102015-4-21 22:02:10.8072570 0.696176161 change inisal value
236112015-4-21 22:02:11.0502570 0.696176161change inisal value
236122015-4-21 22:02:11.1202570 0.696176161 change inisal value
236132015-4-21 22:02:11.2452570 0.696176161change inisal value

View 9 Replies View Related

SQL Server 2008 :: Non Blank / Non Null Value From Table To Update Same Table

Mar 13, 2015

Here is the sample data for table.

create table #sample (emp varchar(max),data1 varchar(max),data2 varchar(max), Rdate date)
insert into #sample (emp,data1,data2,rdate)
values('john','','',getdate())

insert into #sample (emp,data1,data2,rdate)
values('tony','','',getdate())

[Code] ...

I need to update the data1, data2 from yesterday or before but they should be non blank and non null and update to todays data.

It means, I can't update to 2015-03-12 values to 2015-03-13, as they are blanks or nulls.

So I need to go 2015-03-11 and check the values, if they are not blank , i need to update with todays values.

Before the update :

empdata1data2Rdate
john3/13/2015
tony3/13/2015
john3/12/2015
tony3/12/2015
johncd3/11/2015
tonyab3/11/2015

After the update:

empdata1data2Rdate
johncd3/13/2015
tonyab3/13/2015
john3/12/2015
tony3/12/2015
johncd3/11/2015
tonyab3/11/2015

View 4 Replies View Related

SQL Server 2008 :: Alter Table Triggers Recreate Table?

May 26, 2015

which ALTER TABLE/ALTER COLUMN- Statement has a Recreate Table as result ?

View 2 Replies View Related

SQL Server 2008 :: Update A Table With Date When There Are New Records In Another Table

Sep 16, 2015

I have a scenario where I have to Update a table with date when there are new records in another table

For example:

I load ODS table with the data from a file in SSIS. the file has CustomerID and other columns.

Now, when there is new record for any customerID in Ods, then Update the dbo table with the most recent record for every CustomerID(i.e. update the date column in dbo for that customerID). Also Include an Identifier that relates back to the ODS table. How do I do this?

View 8 Replies View Related

SQL Server 2008 :: How To Get Column From Another Table And Insert Into Other Table

May 18, 2015

I have two table 'Cal_date' and 'RPT_Invoice_Shipped'.Table cal_data has column month_no, start_date and end_date. And table RPT_Invoice_Shipped has columns Day_No, Date, Div_code, Total_Invoiced, Shipped_Value, Line_Shipped, Unit_Shipped, Transaction_Date.

I am using below insert statment to insert data in RPT_Invoice_Shipped table.

insert into [Global_Report_Staging].[dbo].[RPT_Invoice_Shipped]
(Day_No, Date, Div_code, Total_Invoiced, Transaction_Date)
select , CONVERT(DATE,Getdate()) as Date, LTRIM(RTRIM(div_Code)),
sum(tot_Net_Amt) as Total_Invoiced, (dateadd(day, -1, convert(date, getdate())))
from [Global_Report_Staging].[dbo].[STG_Shipped_Invoiced]
WHERE CONVERT(DATE,Created_date )=CONVERT(DATE,Getdate())
group by div_code

while inserting in column Day_No in RPT_Invoice_Shipped table, I have to use formula (Transaction_Date-start_date+1) where Transaction_Date from STG_Shipped_Invoiced and start_date from Cal_date table. I was using datepart (mm, Transaction_Date) so it gives month_no, and this month_no we can join with month_no of Cal_date table and fetch start_date from Cal_date table, so that we can use start_date for formula (Transaction_Date-start_date+1).

But I am getting difficulty to arrange this in above query. how to achieve this?

View 1 Replies View Related

SQL Server 2008 :: Replicating Table A Into Table B Within The Same Database

Jun 9, 2015

I am trying to replicated table A into table B withing the same server and the same database.

I am getting an error message when trying to configure the subscription: You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database.

Is there any workaround for this?

I am using SQL Server 2008 R2.

View 2 Replies View Related

Limiting Size Of A Table In SQL Server

Aug 14, 2004

hi,

im doing network monitoring app where basically i run a checks on servers every few minutes and log the data to a table. Naturally the table can get big, quite quickly. What I want is to be able to overwrite the table data at the start of each new day. Alternatively, rollup the data into a daily or weekly packets and then overwrite table data. How do i do this?

View 1 Replies View Related

SQL Server Table Size /Fragmentation

Jun 8, 2000

Hi!,

I have a process(Peoplesoft) in SQL Server which takes long time to finish. I am looking for is there any way to find out fragmentation of the table to defagment it or is there any way to allocate the size for particular table. Couple of users are running the process at same time but as SQL Server has table/page locks it locks and releases after the job is done. Can i make it row level lock by executing sp_tableoption procedure. I would appreciate for all your suggeations.

Thanks,
Mohana

View 4 Replies View Related

VS 2008 Setup Project - Size Limits

Dec 14, 2007

Same issue in VS 2005. Using a setup project (or ClickOnce) there seems to be a limit to how large a content file can be. I'm cross-posting in setup and ClickOnce, but thought that other SQL CE developers may have had this issue.
---------------------------------------------------------
I cannot find a reference to this in the online help, and find no answer here. I have some SQL CE databases that are marked as 'content' in a project. One is less than a mb, the other is over 600mb. The larger one will always break the setup build if I leave it marked as content. Also will break a 'clickOnce publish' build.


How does one use the MS Visual Studio tools for setup when a file size is too big?

What is the max file size limitation?

I need to deploy with ClickOnce, but find it difficult to determine the directory that my large file will need to be copied to afterwards since a ClickOnce install uses such a confusing path to install to. So, if the workaround is to fire a script post install, it would be a problem to discover the path for that file to be copied to.-----------------------------------------------------------------

View 1 Replies View Related

SQL Server 2012 :: Getting Size Of Filtered Table?

Jun 18, 2015

I would like to get the size of the tables in my database BUT as follows:

SIZE OF (SELECT * FROM myTable WHERE Section = 1)

...what is the fastest way to do it?

View 2 Replies View Related

DB Engine :: How To Reduce Size Of Server Table

Jan 20, 2012

I have a table with 99.9% of unused size. How do I reduce the size?I have tried those commands below but they do not work.

ALTER INDEX [VBDATA~0] ON qa2.VBDATA REBUILD
DBCC CLEANTABLE (QA2,"qa2.VBDATA", 0)
WITH NO_INFOMSGS;
GO
ALTER INDEX ALL ON qa2.VBDATA REBUILD

View 10 Replies View Related

DB Engine :: 2008 R2 - Shrink Data File Size?

Nov 9, 2015

I have a sharepoint content database in sql 2008 R2 (WSS_Content) that is at 230Gb size, but has 40% of it is empty space.  This is because we have removed a large amount of old content from sharepoint.  The log file is fine.  I have 60GB left in my drive that host the database files.  I would like to shrink the datafile to get disk space back.  I found that under the files property, the WSS_Content data file's initial size is 228702 MB (220 Gb or so).  

When i try to do a shrink file (data file) from management studio, i see the 60 GB of drive space keep dropping.  So i have to kill the process. what i should do to reduce this data file.

why it keep using up all the free space in the drive when i try to shrink the data file?

View 10 Replies View Related

SQL Server 2012 :: Table Partitioning For 500gb In Size

Feb 25, 2014

Script to do the table partitioning for a 500gb in sliding window technique?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved