SQL Server 2008 :: Table Space Growth Based On Each Year
Oct 28, 2015Does there a way or script to find the rows, space used by the specific tables based on every year.
View 9 Replies
ADVERTISEMENT
SQL Server 2008 :: Return Fiscal Year Based On YYYY-week Format
Nov 6, 2015I have a table where hours are being loaded in a weekly basis. The YearWeek is populated when the data is loaded. The value format of the Year Week is 2015-39, 2015-41, etc. I need to calculate the total hours per Fiscal Year.For example, week '2015-39' will be return FY15 and week '2015-41' will return FY16, and so on. By extracting the year, I can do a group by and have total hours for each year.
Currently, I have it working by splitting the value into year and week and then looping through each year and week, so I can assign the totals to the corresponding FY.select sum(hours) as total, yearweek from tablename group by yearweek...Then I loop through using C#.I can return the FY using an actual date,how to do it for year-week format for any given year.
select CASE
WHEN CAST(GETDATE() AS DATE) >
SMALLDATETIMEFROMPARTS(DATEPART(YEAR,GETDATE()),09,30,00,000)
THEN
DATEPART(YEAR,GETDATE()) + 1 ELSE DATEPART(YEAR,GETDATE())
END AS FY
SQL Server 2008 :: Deleting Data (rows) From Table To Reclaim Space?
Feb 11, 2015I have a table 300+GB. it holds 10 years of Data. I need to delete 5 years of data and put it to another server so I can have more space.
If I delete 5 years of data, Transaction log gets so huge and size of the database even gets bigger because of the .ldf file which even gets bigger! I think I can shrink the log file and the data file. Is this the best way to do it?
SQL Server 2008 :: Estimate Forecast Space For Non-clustered Index On A Table
Apr 22, 2015What is the best way to forecastestimate space for non-clustered index on a table?
Example :
Table name : Test123
Row : 170000000
Reserved : 18000000 KB
Data : 70000000 KB
Index: 40000000 KB
Note: Test123 already has clustered index and 2 non clustered indexes.
SQL Server 2008 :: Transaction Log Growth
Feb 5, 2015I received alerts from a couple of the production servers last night stating that the log file is running out of space. So, I took some log backups and shrunk those files. However, I would like to find out what made the log grow like that. After all, I have t-log backups scheduled every 30 minutes.Is there a way I can find out the reason behind the log growth?
View 9 Replies View RelatedSQL Server 2008 :: Get Previous Values Based On Separate DB / Table?
Apr 24, 2015I am pulling down out of range values from a single table on one database to a different table on a different database on a different server (one i have full access to). Basically, it looks something like this:
id1 value1 prev_value1 value2 prev_value2 date prev_date
id2 value1 prev_value1 value2 prev_value2 date prev_date
id3 value1 prev_value1 value2 prev_value2 date prev_date
all the "prev"'s are null. I want to do one do one query that will get me the previous values and dates for each id from the original database. how to do this.
SQL Server 2008 :: How To Monitor Database Growth
May 5, 2015I need to monitor my database growth, as few of databases are growing rapidly. My client wants the growth list of my databases. have report of database growth of specific databases, at least of one month.
View 3 Replies View RelatedSQL Server 2008 :: MDF File Growth Datewise
Jun 30, 2015Script to find DATEWISE MDF FILE GROWTH Like
30/6/2015 10%
1/7/2015 8%
SQL Server 2008 :: Perform Checksum Based On The Source Table Data?
Aug 10, 2015I'm trying to load data from old SQL server 2000 to new SQL server 2014. I need to do a checksum to check if all the source data is loaded in the target database(SQL server 2014). I've created the insert statement for the same which works. I need to use checksum to make sure all the source rows are loaded in the target table. I haven't done checksum before.
Here is my insert statement:
INSERT INTO [Test].[dbo].[Order_tab]
([rec_id]
,[date_loaded]
,[Name1]
,[Name2]
,[Address1]
,[Address2]
[code]....
SQL Server 2008 :: Auto-growth Configuration Keeps Reverting?
Feb 28, 2015I have a SQL Server 2008 R2 database.
I have modified the autogrowth configuration several times. The strangest thing... they keep reverting back to the original configuration.
SQL Server 2008 :: Transactional Replication And Subscriber Log Growth Errors
Jun 14, 2015I am getting the error The transaction log for database 'ReplicationDB' is full due to 'LOG_BACKUP'.log_reuse_wait_desc from sys.databases is showing logbackup
The database is subscribed database. We configured transactional replication. But the transactional replication is getting errors and failed. Is there relation b/n this replication failures and log growth in subscriber db?
SQL Server 2008 :: Virtual Log Files And Determining Right Growth Size
Oct 14, 2015Any 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.
Return Most Recent Membership Based On Year Stored In Joined Table
Jul 23, 2005Hello-I'm fairly new to writing SQL statements and would greatly appreciatesome help on this one.I'm working on a project for a non-profit that I volunteer for. Partof the database tracks membership using tables like this:PersonInfo-------------------PersonID (primary key)FirstNameLastNameetc..PeopleMemberships-------------------PPLMembershipIP (primary key)PersonIDMembershipTypeIDFeePaidMembershipTypes--------------------MembershipTypeID (primary key)MembershipYearStandardFeeMembershipDescription (varchar)Just because a person is in PersonInfo, doesn't mean they have anythingin PeopleMemberships (they can be in the databse for other reasons andnot have or have ever had a membership).Membership fees vary by year and type of membership and they want toretain a history of a person's memberships.What I'm looking to do here is write a query (a view in SQL Server)that will return the following InfoPersonID, MostRecentMembershipYear, FeePaidForThatMembership,DescriptionOfThatMembershipI'm thinking that I'd use max(MembershipYear), but that requires groupby for the other columns, so I'm getting all of the people'smemberships returned.I'm pretty sure this can be best done with a subquery, but I'm not surehow.Can someone please point me in the right direction or provide a samplethat I can learn from?Kindly,Ken
View 4 Replies View RelatedSQL Server 2008 :: No Option To Automatically Partition Table Based On Unique Values Of Column?
Jun 17, 2015A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.
View 4 Replies View RelatedFree Space Growth
Sep 19, 2005Hi,
While I am importing few data to my SQL Server (2000) database, the free space is gettig increased in GBs. The database's File Growth is 1 MB (same for both Data Files and Transaction Files). The same database I restored on another PC and did the same process, it is working fine as there is no enormous growth in Free Space.
Any idea why this is
Regards,
Eldho
SQL Server 2008 :: Monitor Database Growth And If Any DB Grown By 20% Send Mail Alerts
Apr 30, 2015Is there any automated script available to - "Monitor Database Growth and if any DB is grown by 20%, sending mail alerts"? If not, what is the approach to write the T SQL script ?
View 5 Replies View RelatedSQL Server 2008 :: Bulk Import And Create New Table Based On Header Fields Of Imported File (XLXS)
Sep 11, 2015I have a record in an Excel format (Excel 2010) and I would like to bulk import that into SQL Server 2008 and also while importing, SQL Server will automatically create a new table based on the header fields or row of the source file.
I am not sure if SQL Server 2008 has this capabilities.
Reporting Services :: Matrix - Percentage Growth Same Period Previous Year?
Aug 4, 2015I'm trying to creating the following matrix in SSRS 2008R2 (with more product categories than shown here)The matrix only shows the last two year per product.
The matrix in the report builder looks like this:
For me it's unclear what the expression should be. (or perhaps I need to make a calculation in my TSQL?)
As Row Groups in the matrix I've got:
Product; Year
As Column Groups i've got Month
I can't get it to work. Previous doesn't seem to work in this case (only when I got a total of each year (e.g. one column instead of 12))
SQL Server 2012 :: Select Records Based On Year
Aug 17, 2015I am trying to select records based on a year.
DECLARE @Date
SET @Date = 2012
DECLARE @Year int
SET @Year = (SELECT DATEPART(yyyy,@Date))
SELECT @Year AS Year
--SELECT *
-- FROM [Orders].[dbo].[Orders] od
-- WHERE .Orderl_Date < @Date;
----WHERE DATEPART(yyyy,@Date)= @Year
----WHERE DATEPART(yyyy,od.Order_Date)= @ArchiveYear
SQL Server 2012 :: Select Records Based On Year?
Aug 17, 2015DECLARE @Date
SET @Date = 2012
DECLARE @Year int
SET @Year = (SELECT DATEPART(yyyy,@Date))
SELECT @Year AS Year
--SELECT *
-- FROM [Orders].[dbo].[Orders] od
-- WHERE .Orderl_Date < @Date;
----WHERE DATEPART(yyyy,@Date)= @Year
----WHERE DATEPART(yyyy,od.Order_Date)= @ArchiveYear
SQL Server 2008 :: Month Name And Year (2 Digits) Format
May 12, 2015How to convert a date to the format as Month name and year(2 digits).
For e.g.- Jan 14, Feb 14......Mar 15
SQL Server 2008 :: How To Calculate Number Of Days Since Start Of Fiscal Year
Jul 29, 2015Our fiscal year starts on July 1st. Each month they call a period - so July is period 1, August is period 2, etc.
They are wanting a report that pulls numbers for a given period. There are parameters for them to select the fiscal year and the fiscal period, and then it calculates the numbers for that period. That part works fine.
Now they want me to do some calculations, and one of them is to divide one of the numbers by the # of days since the fiscal year. So if they choose July, it would be 31 days. If they choose August, it would be 61 days, etc. How can I set this up to calculate the number of days when they really aren't entering a start date, it's just a fiscal year and period.
Is there a way to calculate a date field that is 07/01/xxxx where xxxx is the fiscal year they chose? Also a way to calculate a date field that would be the last date of the month for the fiscal period and year they chose?
I suppose I could add 2 other parameters where they enter the start of the current fiscal year, and the last day of the period they're running it for, and use a datediff to calculate that. Just seems kind of redundant.
SQL Server 2008 :: DB Showing Space Available 0.00 MB
Mar 11, 2015I've just checked the properties of one of our DB's running on SQL2008 & the properties within SSMS are showing 0.00MB space available.
The DB is setup using the simple Recovery model & is being backed up nightly.
The Data file has been set at an intial size of 9449MB with Autogrowth by 10 percent unrestricted growth.
The Log File has been set at an intial size of 14199MB with Autogrowth by 10 percent unrestricted growth.
I have checked the SQL logs and no errors are being reported. There is 79.8gb available on the partition where the DB resides.
SQL Server 2008 :: How To Convert Free Space Value To MB Or In GB
Mar 9, 2015wmic logicaldisk where "DeviceID='C:'" get FreeSpace /format:value
FreeSpace=231379468288
SQL Server 2008 :: Track Unallocated Space On Database?
Apr 1, 2015I am trying to track unallocated space (because on my database growth is not kicking in) So using below query but not working!!! Is there any way I can track 'database size' and 'unallocated space' on a single database?
create table A(
Rundate DATETIME NOT NULL DEFAULT(GETDATE())
,DatabaseName varchar(100)
,Database_size varchar (100)
,unallocatedSpace varchar (100)
,reserved varchar(100)
[code].....
exec AdventureWork..sp_spaceused ------not working------
SQL Server 2008 :: Not Finding A Record With A Space In The Middle Of String
May 19, 2015Ok following scenario
create table dbo.ADAssets
(Computername nvarchar(max))
insert into dbo.ADAssets
values
('AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565')
So if execute the following
select Computername
from dbo.ADAssets
where Computername like
'AIRLBEOF3565%'
I get both records,but if I do this
select *
from dbo.ADAssets
where Computername in
(
'AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565'
)
I only get AIRLBEOF3565
So the big picture is that I need to compare 2 tables to find records that match & don't but that I get matches that shouldn't be & matches that aren't.
SQL Server 2008 :: How To Delete Or Drop Index To Free Up Space
May 31, 2015I am new to mssql server. There is a table on one of my databases that occupies a lot of space. And the space usage is as follows: (all values in KB)
reserved: 42329064
data: 16272288
index: 26050032
unused: 6744
This table takes up almost 80% of my database size, and the information is this table just captures the time spent by a user on the website(not very critical data)
I would like to know how to delete the entire index (which is what is occupying most space) to free up disk space. the index is a clustered index.
SQL Server 2008 :: File Group Is Running Out Of Space Frequently
Sep 1, 2015We are receiving following alerts frequently about 1:40 AM in the morning. We have backups running on 11:00 PM everyday and rebuild job running at 2:00 AM. Not sure the exact cause of this error.
Error:
The file group "PRIMARY" for the database "tempdb" in SQL instance "MSSQLSERVER" on computer "XYZ" is running out of space.
tempdev Initial size : 133,100 MB Growth: By 10 percent, Limited to 140000 MB
templog Initial Size : 5,475 MB Growth: By 10 percent, Unlimited
SQL Server 2008 :: Handle Space Between Multi-value Parameter Values In SSRS?
Jul 8, 2015how to handle space between multi-value parameter values in SSRS. For e.g. if the values are as follows -'KLO LUG', 'HGY KIU', 'LOT JUY', I know I can use the split function for the commas but its the space between the value which is the problem.
View 3 Replies View RelatedSQL Server 2008 :: Fill Factor And SSD Based SAN?
Jun 14, 2015I am about to rebuild all my indexes on a database that is very heavily fragmented. In looking at the report, seems that 80% or more tables are 90%+ fragmented.
My understanding is that fill value value is used for performance reasons. Our shiny new backend SAN is 100% SSD. If solid state can provide a sub-millisecond response, is fill factor still necessary at the cost of additional space being used used?
SQL Server 2008 :: How To Get Output Based On Sequential Number
Feb 13, 2015The below data come from table table1. Instead of below result Ex1: I need output similar to the ex2.
Ex1:
CaseNumberStart CaseNumberEndExported
15000013150000131
15000014150000141
15000504150005041
15000505150005051
Ex2:
CaseNumberStart CaseNumberEndExported
15000013150000142
15000504150005052
How to get the result similar to Ex2, instead of Ex1. (ie., case-number is in sequential order then no need to break), And it should suit large dataset, I will finetune, if any performance issue.
SQL Server 2008 :: How To Sum Values Based On Quarter And Month
Mar 20, 2015We had a requirement that need to sum the data based on quater we will be having 12 months data in the system for an year suppose we have 12 records for 2014 year. jan month sales data should be same when we were in feb month it should sum jan+feb sales and should show in sales column whereas we were in march month it should sum jan+feb+mar sales, then same for next quater also apr month it wil be same value in may it should be apr+may in may sales value etc ....
We will be having date column values as 201401,201402,.....
How can we implement in sql sever performance should be good.
SQL Server 2008 :: How To Change Columns Based On Another Field Value
Jun 10, 2015I would like to pull data from two seperate columns based on the vaule for MakeFlag. So if MakeFlag = 0 I would like the description to show but anything else I would like catalog description to show up.
DECLARE @MyVari varchar(20)
SELECT [ProductID]
,[prod].[Name]
,[ProductNumber]
,[MakeFlag]
[Code] ....